This post describes how to get the schema of a table in BigQuery. Its actually quite straightforward (I mean you can use standard JDBC methods to get it) if one uses the BigQuery JBDC connector like below:
try (Connection connection = BigQueryJdbcConnection.createJdbcConnection(gcpProjectId)) {
// https://www.baeldung.com/jdbc-database-metadata
DatabaseMetaData databaseMetaData = connection.getMetaData();
try(ResultSet columns = databaseMetaData.getColumns(null,null, table, null)){
while(columns.next()) {
String columnName = columns.getString("COLUMN_NAME");
String columnSize = columns.getString("COLUMN_SIZE");
String dataType = columns.getString("DATA_TYPE");
String isNullable = columns.getString("IS_NULLABLE");
String isAutoIncrement = columns.getString("IS_AUTOINCREMENT");
String typeName = columns.getString("TYPE_NAME");
int sqlDataType = columns.getInt("SQL_DATA_TYPE");
System.out.printf("%s %s %s %s %d %s %s\n", columnName, columnSize, typeName, dataType, sqlDataType, isNullable, isAutoIncrement);
}
}
}
BigQueryJdbcConnection is a class I have defined. To get the JDBC connection, first you have to download the bigquery-jdbc-connector jar (it is not available in Maven Central), then mvn install it to Maven repository, and after that add reference to it in pom.xml:
<dependency>
<groupId>com.simba.googlebigquery</groupId>
<artifactId>bigquery-jdbc-connector</artifactId>
<version>1.3.0</version>
</dependency>
After that, obtaining a JDBC connection is simply a matter of calling (in below I am using ADC – application default credentials):
DriverManager.getConnection(buildConnectionUrl(projectId, AUTH_TYPE.APPLICATION_DEFAULT_CREDENTIALS));
and you can build the connection URL as:
private static String buildConnectionUrl(String projectId, AUTH_TYPE auth) {
return String.join(";",
PREFIX,
String.format("OAuthType=%s", getAuthType(auth)),
String.format("projectId=%s", projectId),
"LogLevel=4");
}
where:
private static String PREFIX = "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443";
private enum AUTH_TYPE { APPLICATION_DEFAULT_CREDENTIALS, SERVICE_ACCOUNT, USER_CREDENTIALS, ACCESS_TOKEN };
private static int getAuthType(AUTH_TYPE authType) {
switch (authType) {
case SERVICE_ACCOUNT:
return 0;
case USER_CREDENTIALS:
return 1;
case ACCESS_TOKEN:
return 2;
case APPLICATION_DEFAULT_CREDENTIALS:
return 3;
}
throw new IllegalArgumentException();
}
Let me know how it goes for you. Alternatively, you can query the schema without using the JDBC connector as follows:
SELECT column_name, ordinal_position, is_nullable, data_type, is_generated, is_hidden, is_partitioning_column FROM `some-project.some-dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'some-table'
siddharth's space 