CODEWITHSUNDEEP
google-bigquery
user3132353
user3132353

Reputation: 129

Can we get Column Name from specific Table in Google BigQuery?

Can we get Column Name from specific Table in Google BigQuery??

Let me know query for this activity.

I tried this but cant got result...

  SELECT column_name FROM publicdata:samples.shakespeare
  OR
  SELECT schema FROM publicdata:samples.shakespeare

Upvotes: 2

Views: 12571

Answers (6)

Yun Zhang
Yun Zhang

Reputation: 5518

Use INFORMATION_SCHEMA to get column names with SQL:

SELECT column_name, data_type
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.COLUMNS` 
WHERE table_name = 'shakespeare'

It gives you:

+-------------+-----------+
| column_name | data_type |
+-------------+-----------+
| word        | STRING    |
| word_count  | INT64     |
| corpus      | STRING    |
| corpus_date | INT64     |
+-------------+-----------+

Upvotes: 1

dekkerr
dekkerr

Reputation: 67

If I understand you correctly, you would like to do a tables.list or tables.get and not a jobs.query.

This is how it works in google apps script:

var results = BigQuery.Tables.list(projectId, datasetId, optionalArgs);

Or by the API:

GET https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables

https://developers.google.com/bigquery/docs/reference/v2/tables/list

GET https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables/tableId

https://developers.google.com/bigquery/docs/reference/v2/tables/get

Otherwise, you can query like this SELECT * FROM [] limit 0 and write some procedure that looks at the column names.

Upvotes: 0

user8697791
user8697791

Reputation:

Without any queries, on the Classic UI, you can proceed as follow:

  • click on the blue down arrow on the left panel
  • Switch to project, then Display project...
  • on Project ID, write the name of the project (in your case you have publicdata:samples.shakespeare, your project is publicdata)
  • now, this project appears on the left panel
  • select the dataset (in your case it is Sample)
  • select the table (in your case it is shakespeare)
  • finally, in the middle of the screen you should see three tabs: Schema, Details, Preview.

Upvotes: 0

hkanjih
hkanjih

Reputation: 1301

A sample using python in Jupyter:

SERVICE_ACCOUNT = 'sa_bq.json'

!pip install google-cloud
!pip install google-api-python-client
!pip install oauth2client    

from google.cloud import bigquery

client_bq = bigquery.Client.from_service_account_json(SERVICE_ACCOUNT)
table = client_bq.get_table('bigquery-public-data.samples.shakespeare')
print(list(c.name for c in table.schema))

Upvotes: 0

user3132353
user3132353

Reputation: 129

I got result using Java:

Tables tableRequest = bigquery.tables();
Table table = tableRequest.get(projectName,datasetName,tableName).execute();
List<TableFieldSchema> fields = table.getSchema().getFields();

Upvotes: 3

jacek2v
jacek2v

Reputation: 590

1.You can use commandline tool (https://developers.google.com/bigquery/bq-command-line-tool#gettable): bq show :.

$ bq show publicdata:samples.shakespeare
tableId      Last modified                  Schema
------------- ----------------- ------------------------------------
shakespeare   01 Sep 13:46:28   |- word: string (required)
                              |- word_count: integer (required)
                              |- corpus: string (required)
                              |- corpus_date: integer (required)

2.BigQuery Browser Tool : https://developers.google.com/bigquery/bigquery-browser-tool#examineschema

3.Or use BigQuery API: https://developers.google.com/bigquery/docs/reference/v2/tables/get

Upvotes: 3

Related Questions