Bigquery transform service for Google merchant center data

[zohaib-jamshed]

Hello, I wanted to know if, Is there any way to filter out the categories when we load the data into BQ from the transform service for Google merchant center.
As I'm only interested in some 10-15 categories but transform service load the data of all categories which is a waste of storage. I know I can delete the record through a query.
Any kind of support?

[ms4446 · Accepted solution]

Yes, you have the ability to filter categories while loading data into BigQuery from the Transform Service for Google Merchant Center. Here's how you can do it:

Here are the steps:

1. In the Google Cloud console, go to the Transform Service page.
2. Click the Load data tab.
3. In the Source section, select Google Merchant Center.
4. In the Destination section, select BigQuery.
5. In the Advanced options section, click the Edit button.
6. In the Categories field, enter the list of categories that you want to include.
7. Click Save.

By doing this, the Transform Service will only load data corresponding to the categories you've specified. This not only conserves storage space but also simplifies data querying.

If you wish to remove records of categories that don't interest you, you can use the following query:

DELETE FROM my_dataset.my_table WHERE category IN ('category1', 'category2', 'category3');

This query will eliminate all records in the 'my_table' table where the 'category' field matches 'category1', 'category2', or 'category3'.

[ms4446 · Accepted solution]

No problem. I understand that it can be a bit confusing to find the advanced options in the Google Cloud console. Here are the steps on how to find the advanced options for filtering categories when loading data into BigQuery from the Google Merchant Center transform service:

1. Go to the Google Cloud console: https://console.cloud.google.com/.
2. Click the Transform Service page.
3. Click the Load data tab.
4. In the Source section, select Google Merchant Center.
5. In the Destination section, select BigQuery.
6. Click the Advanced optionssection.
7. Click the Edit button.
8. In the Categories field, enter the list of categories that you want to include.
9. Click Save.

The advanced options should now be visible. You can enter the list of categories that you want to include in the Categories field. Once you have entered the list of categories, click Save. The transform service will only load data for the categories that you have specified.

[ms4446 · Accepted solution]

Yes, you have the ability to filter categories while loading data into BigQuery from the Transform Service for Google Merchant Center. Here's how you can do it:

Here are the steps:

1. In the Google Cloud console, go to the Transform Service page.
2. Click the Load data tab.
3. In the Source section, select Google Merchant Center.
4. In the Destination section, select BigQuery.
5. In the Advanced options section, click the Edit button.
6. In the Categories field, enter the list of categories that you want to include.
7. Click Save.

By doing this, the Transform Service will only load data corresponding to the categories you've specified. This not only conserves storage space but also simplifies data querying.

If you wish to remove records of categories that don't interest you, you can use the following query:

DELETE FROM my_dataset.my_table WHERE category IN ('category1', 'category2', 'category3');

This query will eliminate all records in the 'my_table' table where the 'category' field matches 'category1', 'category2', or 'category3'.

[zohaib-jamshed]

Hi, Thanks for your response. But I'm really sorry to ask you again that I can not find the advanced options to enter the categories list. 

Can you guide me futher please.

[ms4446 · Accepted solution]

No problem. I understand that it can be a bit confusing to find the advanced options in the Google Cloud console. Here are the steps on how to find the advanced options for filtering categories when loading data into BigQuery from the Google Merchant Center transform service:

1. Go to the Google Cloud console: https://console.cloud.google.com/.
2. Click the Transform Service page.
3. Click the Load data tab.
4. In the Source section, select Google Merchant Center.
5. In the Destination section, select BigQuery.
6. Click the Advanced optionssection.
7. Click the Edit button.
8. In the Categories field, enter the list of categories that you want to include.
9. Click Save.

The advanced options should now be visible. You can enter the list of categories that you want to include in the Categories field. Once you have entered the list of categories, click Save. The transform service will only load data for the categories that you have specified.

[zohaib-jamshed]

Hello, Am I doing something wrong?
I'm following these steps to load the merchant center data console->bigquery->data transfer from the left panel-> create a new transfer. As you can see above in the screen shot, I still did not found the advanced option for editing the categories list.

[ms4446 · Accepted solution]

You can only specify the categories that you want to include when you load data from the Transform Service for Google Merchant Center using the command-line tool.

To do this, you need to use the gcloud command-line tool. Here is an example of how to use the gcloud command-line tool to filter categories when you load data from the Transform Service for Google Merchant Center:

gcloud dataflow jobs run transform --source_format=google_merchant_center --destination_format=bigquery --categories="Apparel & Accessories,Electronics"

This command will run the Transform Service for Google Merchant Center and load data into BigQuery. The data will only include products that are in the categories "Apparel & Accessories" and "Electronics".

For more information on how to use the gcloud command-line tool to load data from the Transform Service for Google Merchant Center, please see the gcloud documentation: https://cloud.google.com/dataflow/docs/reference/rest/v1b3/projects.jobs