I would like to do sharding the table on BigQuery

Matillion ETL
1

Hi all.

​

I'm trying to load data from Amazon RDS to BigQuery using Database Query Component and woluld like to do sharding the table on BQ.

​

I attached a orchestration image below.

0694G00000Hb2S6QAJ_0D54G0000824sQjSAI
0694G00000Hb2S6QAJ_0D54G0000824sQjSAI1291×289 36.8 KB

First, I defined ​’today’ and ‘yesterday’ by Python script as a job variable.

Then, what I would like to do is when I create table, the table name will be like ‘media_tbl_20211110’.

So, I set ‘media_tbl_${yesterday}’ to New Table Name on Create Table component, but it shows error like ‘Name may only contain letters, numbers, and underscores.’

 

Is it correct that It means the job variable can’t set to New Table Name on Create Table component??

 

How can I do sharding using Database Query component??

 

Thanks a lot.

 

 

2

It works for us if we set the variable to be the full table name ie media_tbl_20211110 and then the New Table Name to that variable.

Eg set a new variable new_table_name to 'media_tbl_' + yesterday in your python script and then set the New Table Name on Create Table component to ${new_table_name}

3

Dear @SDIMB​

Thank you for your advise and sorry for late reply.

​

I could do sharding as you advised me.

I really appreciate it and hope it will be useful for other customer.

Thank you.

​

​