To do the same using Google Cloud SDK, run the commands given as follows. Using your browser, download the file from this URL: https://github.com/hthirukkumaran/Learning-Google-BigQuery/blob/master/chapter1/employeedetails.csv:
- Upload the file to Google Cloud Storage using the gsutil utility. Make sure the gcloud utility is pointing to the correct Google Cloud project.
- To see which project the gcloud utility is configured to use, type the following command:
gcloud info
- To upload the file to a Google Cloud Storage bucket, run the following command and replace the bucket name and filename as per your needs:
gsutil cp <full path of the file> gs://bucketname/filename
The bq command-line utility does not have a direct option to create a partition table. The workaround is to create a table with a date as a suffix in its name and move that data to a partition table.
The following command creates a new table with the schema specified and imports data from the Google Cloud Storage files.
- Replace the bucket name and filename with your bucket name and filename. The first row in the file is a header row; hence the --skip_leading_rows flag is specified:
bq load --skip_leading_rows 1 PartitionedTablesDemo.Temp_EmployeeDetails_20171101 gs://myfirstprojectbucket201706/employeedetails.csv Employee_ID:INTEGER,Employee_First_Name:STRING,Employee_Last_Name:STRING,Employee_Joining_Date:DATE,Employee_location:STRING
- To create a partition table from this table, use the partition command in the bq utility as shown in the next code.
- Replace the dataset name in the source table and destination table. In the following command, PartitionedTablesDemo.Temp_EmployeeDetails_ is the source table prefix. From any table in the dataset that starts with this prefix and has date in YYYY/MM/DD format at the end, the data will be copied to the new partition table, PartitionedTablesDemo.Employee_Partitioned_Table. The date in the name of the table will be used to insert into the _PARTITIONTIME column:
bq partition PartitionedTablesDemo.Temp_EmployeeDetails_ PartitionedTablesDemo.Employee_Partitioned_Table