Solved: How to transform Table in us-central1 to table in ... - Page 2

pnr · 10-02-2023 03:09 PM

Hello,

I have a table in us-central1 which is referenced in Dataform. I want to apply some transformation on it and write to a table in US location. How can I achieve that?

I have configured the

defaultLocation to us-central1 in dataform.json

"defaultLocation": "us-central1"

In my_table.sqlx

config {
type: "incremental",
schema: "my_schema",
protected: true,
location: "US"
uniqueKey: ["KeyId"]
}

But, I was unable to access the my_table.

Can anyone help in letting me know how to achieve my use case? Thanks.

ms4446

I understand your use case better now. If you're dealing with a change stream and need continuous transformations, you'll want a solution that can handle real-time or near-real-time data processing.

In BigQuery and Dataform, you can't directly query tables across regions. However, you can set up a process that allows you to continuously transform and move data from one region to another. Here's a suggested approach:

Data Transfer:
- Use a method suitable for your needs (e.g., BigQuery Transfer Service, custom scripts, etc.) to move data from the us-central1 region to the US multi-region location.
Dataform Script:
- In your Dataform script, reference the table in the US multi-region location and apply the desired transformations. Here's an example:

-- Reference the table in the US multi-region location and apply transformations.
SELECT
column1,
column2,
column3 * 2 AS new_column
FROM
`project_id.dataset_name.table_name`

Dataform Configuration:
- Ensure your Dataform project is set up to connect to the BigQuery project and dataset in the US multi-region location.
Running the Transformations:
- Use the Dataform web interface or CLI to run your scripts and apply the transformations. To run the script, use the command:

dataform run

By following this approach, you can ensure that your change stream data in us-central1 is continuously transformed and written to the US multi-region location in BigQuery.

View solution in original post

How to transform Table in us-central1 to table in US data location