CODEWITHSUNDEEP
google-bigquery
nithin
nithin

Reputation: 781

How to get show create table in Big Query web UI

I want to get Create table script for an existing Table basically DDL from web UI or something similar to Show create table . I have googled couldn't find anything relevant

Upvotes: 18

Views: 41245

Answers (3)

Murta
Murta

Reputation: 2195

Cool news! Launched today, now you can get the DDL from INFORMATION_SCHEMA.TABLES

Here is the example from Google Documentation:

SELECT
 table_name, ddl
FROM
 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
WHERE
 table_name="population_by_zip_2010"

enter image description here

Upvotes: 50

Xiaoxia Lin
Xiaoxia Lin

Reputation: 746

There is a Feature Request to have equivalent of SHOW CREATE TABLE MySQL command. As you can see from the comment#2 from Elliott, it's not likely to be implemented. However you can "recreate a CREATE TABLE statement using INFORMATION_SCHEMA", for more detail, please check this public issue.

Upvotes: 4

ch_mike
ch_mike

Reputation: 1576

I don't think you can retrieve a CREATE statement from an existing table in BigQuery, but you can do:

-Obtain the table schema:

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE] > ./schema.json

-Create a new table from the schema file:

bq mk --table --description [DESCRIPTION] [PROJECT_ID]:[DATASET].[TABLE] ./schema.json

You can review the additional settings for table creation here.

Upvotes: 19

Related Questions