Download the sample file from this URL and upload it to a Google Cloud Storage bucket: https://github.com/hthirukkumaran/Learning-Google-BigQuery/blob/master/chapter1/employeedetails.csv. And note down the bucket name.
- Click on the Create new table option under the Dataset menu.
- To create a partition table, enable the partition option by choosing Day in the Partitioning drop-down as shown in the following screenshot. Enter the file from Google Cloud Storage to import data into the new table.
- Click on the Create Table button to create the table and import the data from the file:
Once the table is created, a new column, _PARTITIONTIME, will be added to the table.
This is a TIMESTAMP column that will have the value of the date on which a record was inserted.
The time zone for the _PARTITIONTIME is UTC:
- To see the value in the _PARTITIONTIME column, run the next query against the table created in the previous step.
- Replace the dataset name and table name with your dataset name and your table name. The first column will show the output in UTC time. This query uses legacy SQL format:
SELECT TIMESTAMP(_PARTITIONTIME), * FROM [PartitionedTablesDemo.Employee_Details]
The standard SQL format for the preceding query is given as follows. The dataset name and table name are enclosed in a back tick, which is above the tilde operator on the keyboard:
#standardSQL
SELECT CAST(_PARTITIONTIME as DATETIME), * FROM `PartitionedTablesDemo.Employee_Details`