Save large BigQuery results to another project's BigQuery

Question

I need to run a join query on BigQuery of one project, that may return large amount of data (that may not fit in VM's memeory), and then save the results in the BigQuery of another project.

Is there an easy way to do this without loading the data in VM, as data size can vary and VM may not have enough memory to load it?

Answer 1

One method is to bypass the VM for the operation and utilize Google Cloud Storage instead.

The process will look like following

1. Create a GS bucket that both projects has access to
2. Source project - Export the table to the GS bucket (this is possible from the web interface, pretty sure the CLI tools can do it to)
3. Destination project - Create a new table from the files in the GS bucket

Answer 2

to save result of query to a table in any project - you do not need to save it first to VM you should just set properly destination property and of course you need to have write permissions to dataset that contain that table!

Destination property can vary depend on client tool you use
for example, if you are using REST API's jobs.insert you should set below property

configuration.query.destinationTable nested object [Optional]
Describes the table where the query results should be stored. If not present, a new table will be created to store the results. This property must be set for large results that exceed the maximum response size.

configuration.query.destinationTable.datasetId string [Required]
The ID of the dataset containing this table.

configuration.query.destinationTable.projectId string [Required]
The ID of the project containing this table.

configuration.query.destinationTable.tableId string [Required]
The ID of the table. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters.