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??

Answer 1

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();
        }
    }
}