dotConnect for Google BigQuery provides two ways of getting Google BigQuery metadata: the GetSchema method and the information schema tables.

The GetSchema method lets you read schema information without writing queries and parsing the output. All information you may want to obtain is brought to you by a single function in an easy-to-process format. You can get information on datasets, tables, columns, and primary keys. The method is introduced in System.Data.Common.DbConnection.

How To Use

The GetSchema method is available in three overloads, each of them serves its own purpose. All overloads return a System.Data.DataTable object that contains information about schema elements.

public override DataTable GetSchema();

If you call the GetSchema method without parameters, or with the single parameter "MetaDataCollections" (which is actually the same), the table object returned by the method contains two columns. The first column of every row is a keyword allowed to be passed to the method (as the collectionName argument). The second column is the number of restriction values for this keyword (passed through the restrictionValues argument).

public override DataTable GetSchema(string collectionName);

GetSchema with one argument returns general information about the collection queried. For example, GetSchema("Tables") returns the list of the tables and views in the dataset specified in the DataSet Id connection string parameter, or in all datasets of the project if DataSet Id is not specified.

public override DataTable GetSchema(string collectionName, string[] restrictionValues);

In this overload, the first parameter is the name of a collection, and the second parameter is an array of restrictions to be applied when retrieving information. These restrictions specify which subset of the collection is returned. The first restriction of every collection, except the ones that have no restrictions, is the dataset name, followed by the table name and the column or key name. Restriction values are compared with the names for exact equality; wildcards aren't supported. A null or empty restriction value indicates that no restriction is applied. If the second parameter is null/Nothing, it is ignored.

Instead of specifying the metadata collection name as a string constant, you may use the members of System.Data.Common.DbMetaDataCollectionNames and Devart.Data.BigQuery.BigQueryMetaDataCollectionNames as the first GetSchema argument value. The members of these classes are string fields; each field stores the corresponding metadata collection name. It is recommended to use these fields instead of hardcoding collection names as string constants. This allows misspellings to be caught at compile time and enables IntelliSense to suggest all available metadata collection names.

GetSchema Method Reference

The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them.

GetSchema Resultset Columns

For each metadata collection, dotConnect for Google BigQuery returns a DataTable with its own set of columns.

Columns

DataSets

DataSourceInformation

MetaDataCollections

PrimaryKeyColumns

PrimaryKeys

ReservedWords

Restrictions

Tables

Samples

The following code fragment is an elegant way to detect existence of a table.

string tableName = "my_table";
if (myConnection.GetSchema("Tables", new string[] { "my_dataset", tableName }).Rows.Count > 0)
{
  Console.WriteLine("Table " + tableName + " exists in the dataset.");
}

The next sample shows how to retrieve columns information from a table and render it to console.

static void GetTableInfo(BigQueryConnection myConnection, string dataSetName, string tableName)
{
  myConnection.Open();
  DataTable myDataTable = myConnection.GetSchema("Columns", new string[] { dataSetName, tableName });
  for (int i = 0; i < myDataTable.Columns.Count; i++)
  {
    Console.Write(myDataTable.Columns[i].Caption + "\t");
  }
  Console.WriteLine();
  foreach (DataRow myRow in myDataTable.Rows)
  {
    foreach (DataColumn myCol in myDataTable.Columns)
    {
      Console.Write(myRow[myCol] + "\t");
    }
    Console.WriteLine();
  }
  myConnection.Close();
}

Also you can get metadata of a query result set using the GetSchemaTable method of BigQueryDataReader.

See Also

Metadata | Metadata Overview | Information Schema |