How to get datasets name in Google BigQuery including "publicdata"

Question

I create jdbc connection for google BigQuery as follwing

    Class.forName("net.starschema.clouddb.jdbc.BQDriver");
    conn = DriverManager.getConnection("jdbc:BQDriver:"projectID"?transformQuery=true&user="client ID"&password="client secret");

then i get catalog name as follow

 ResultSet m_resultSet = conn.getMetaData().getCatalogs();
 while (m_resultSet.next())
 {
     System.out.println(m_resultSet.getString(4));
 }

But now i am trying to get datasets name. it returns null.

Can i get dataset name of publicdata ?? and How??

I am actually using .Net library and I would like to get a list of all available projects, including "publicdata" — Andrey Belykh, Jul 15 '15 at 18:41
When I go to https://bigquery.cloud.google.com I can see 3 projects (fh-bigquery, gdelt-bq, publicdata). Looks like it is not stored in cookies because I see the same when I opened a different browser. Would be nice if I could somehow get this list — Andrey Belykh, Jul 17 '15 at 21:23

score 1 · Answer 1 · answered Jul 16 '15 at 07:31

You can get list of Projects, Data Sets and Tables by using below code.

To get Public-Data you can use below code

var SampleTableList = Service.Tables.List("publicdata", "samples").Execute();

Because publicdata has only one DataSet (samples), we can't able to add new dataset so this code would work properly.

Modify Properties such as ServiceAccountEmail, KeyFile path and Key Secret etc.

using Google.Apis.Auth.OAuth2;
using System.IO;
using System.Threading;
using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;
using System.Data;
using Google.Apis.Services;
using System;
using System.Security.Cryptography.X509Certificates;

namespace GoogleBigQuery
{
    public class Class1
    {
        private static void Main()
        {
            try
            {
                String serviceAccountEmail = "SERVICE ACCOUNT EMAIL";

                var certificate = new X509Certificate2(@"KEY FILE NAME & PATH", "KEY SECRET", X509KeyStorageFlags.Exportable);

                // SYNTAX: var certificate=new X509Certificate2(KEY FILE PATH+NAME (Here it resides in Bin\Debug folder so only name is enough), SECRET KEY, X509KeyStorageFlags.Exportable);

                ServiceAccountCredential credential = new ServiceAccountCredential(
                   new ServiceAccountCredential.Initializer(serviceAccountEmail)
                   {
                       Scopes = new[] { BigqueryService.Scope.Bigquery, BigqueryService.Scope.BigqueryInsertdata, BigqueryService.Scope.CloudPlatform, BigqueryService.Scope.DevstorageFullControl }
                   }.FromCertificate(certificate));

                //  Create and initialize the Bigquery service. Use the Project Name value
                //  from the New Project window for the ApplicationName variable.

                BigqueryService Service = new BigqueryService(new BaseClientService.Initializer()
                {
                    HttpClientInitializer = credential,
                    ApplicationName = "APPLICATION NAME"
                });


                var SampleTableList = Service.Tables.List("publicdata", "samples").Execute();

                var projectList = Service.Projects.List().Execute();

                foreach (var projectDet in projectList.Projects)
                {
                    var DataSetList = Service.Datasets.List(projectDet.Id).Execute();

                    foreach (var DataSetDet in DataSetList.Datasets)
                    {
                        var TablesList = Service.Tables.List(projectDet.Id, DataSetDet.Id).Execute();
                    }
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("Error Occurred: " + e.Message);
            }

            Console.ReadLine();
        }
    }
}

I know how to get datasets in publicdata, the question is how to get the list of projects that includes publicdata — Andrey Belykh, Jul 17 '15 at 21:27

score 0 · Answer 2 · answered Jul 31 '15 at 16:05

0

Looks like this is the best source where to get the list of public datasets: http://www.reddit.com/r/bigquery/wiki/datasets

answered Jul 31 '15 at 16:05

Andrey Belykh

2,578
4
32
46

score 0 · Answer 3 · answered Apr 23 '16 at 05:25

I know how to get datasets in publicdata, the question is how to get the list of projects that includes publicdata? – Andrey Belykh Jul 17 '15 at 21:27

Programmatically, user can get list of ONLY projects to which he/she have been granted any project role:
Can View , Can Edit or Is Owner

Public Data becomes public via sharing on dataset level with All Authenticated Users

Thus, user CANNOT list projects with public data

How to get datasets name in Google BigQuery including "publicdata"

3 Answers3