Copy partitioned table schema to another table

Use the gcloud bq cli tool

1) export the source table schema to json file. Each item only requires name and type fields.

bq show --schema --format=prettyjson projectId:Dataset.table > emailschema.json

https://cloud.google.com/bigquery/docs/schemas#specify-schema-manual-cli

2) Create the table in destination as partitioned

bq mk --table --schema ./emailschema.json --time_partitioning_type DAY projectId:dataset.table

https://cloud.google.com/bigquery/docs/creating-column-partitions

Comments

Post a Comment

Popular posts from this blog

Using moment.js

const moment = require('moment-timezone'); Calculating duration in seconds from current time to a future time. e.g. the next day const nextDayDateString = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDate = moment(nextDayDateString).tz('America/Los_Angeles'); const nextDayExpireSeconds = Math.round(moment.duration(nextDayDate.diff(moment())).asSeconds()); Calculating unix timestamp of certain date in local time const nextDayDt = moment().tz('America/Los_Angeles').add(1, "days").format("YYYY-MM-DD 00:00:00"); const nextDayDtUnix = moment(nextDayDt).tz('America/Los_Angeles').utc().unix(); Determining date string by the number of days back using local time // Will display date string 30 days back const daysBack = 30; var dateFrom = moment.tz(moment().subtract(daysBack,'days').format('YYYY-MM-DD 00:00:00'), 'America/Los_Angele...
Read more

Changing BigQuery table field names

https://cloud.google.com/bigquery/docs/manually-changing-schemas With sql you can change a BQ table field name e.g. SELECT * EXCEPT(last_activity_ts, invalid_reason), last_activity_ts as evpo, invalid_reason as evpo_invalid_reason FROM dataset.tableName This sql will select all fields except the ones you want to change then change the name using as keyword. In bq options select the table you want to change and replace table option. The table will replace itself with original table content but with new field names. For large datasets you are paying for entire table scan and can be expensive.
Read more