BQ Google Ads Table Saying Duplicate Column

[fazzig]

We have our google ads data updating with our BigQuery database. The SearchQueryStats table has become un-query-able because of a 'Duplicate column' error. I'm not search on that column, nor can I see it duplicated in name within the schema

Query I'm using

SELECT * FROM `database.SearchQueryStats` LIMIT 1000

I get this error

'Duplicate column name 'QueryMatchTypeWithVariant' in definition of view'

When I look at the table scheme there is a QueryMatchTypeWithVariant field and a QueryMatchTypeWithVariant_1 field.

Even when I run

SELECT campaignID, creativeID FROM `database.SearchQueryStats` LIMIT 1000

I'll get the same error despite not searching on the error column.

[Aris_O]

Hi @fazzig,

Welcome back to Google Cloud Community.

The error "Duplicate column name 'QueryMatchTypeWithVariant' in definition of view" suggests that the SearchQueryStats table's duplicate column name is the root of the problem.

QueryMatchTypeWithVariant and QueryMatchTypeWithVariant_1 columns are both included in the schema for the SearchQueryStats table, as you already noted. This indicates that a schema change may have occurred, leading to the addition of a new column with the same name as an existing column.

You can fix this problem by altering your query to specifically pick just one of the duplicate columns. You may, for instance, change your query to include the QueryMatchTypeWithVariant_1 column and leave out the QueryMatchTypeWithVariant_2 field.

You may use the AS keyword to rename the duplicate column to something unique as alternative. 

Once the duplicate column issue has been rectified, you might want to think about upgrading any related queries or views that could be impacted by the schema change.

Here are some documentation that might help you:
https://cloud.google.com/bigquery/docs/nested-repeated?_ga=2.184547686.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/table-clones-create?_ga=2.184547686.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/error-messages?_ga=2.184547686.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/managing-tables?_ga=2.184547686.-1392753435.1676655686
https://cloud.google.com/bigquery/docs/table-clones-intro?_ga=2.184547686.-1392753435.1676655686

[fazzig]

Thanks @Aris_O . This is helpful and I was able to make a temporary change for a day using @BrianBr solution.

The issue is that these tables are automatically created by Google and refreshed each day. The duplicate column appears when the table is updated.

[BrianBr]

Hi @Aris_O ,
I'm experiencing the same exact same problem as @fazzig, 

For me, this started when a new Data Transfer was added automatically, namely; '{oldtransfername}_converted'. In which the source was changed from 'Google Ads (formerly AdWords)' to 'Google Ads - Preview', which I assume was added to prepare for the upcoming depreciation of AdWords transfers.

This new transfer is causing a change in the view of 'SearchQueryStats', duplicating the column 'QueryMatchTypeWithVariant'. One is marked as 'segments_search_term_match_type AS QueryMatchTypeWithVariant', the other is 'NULL AS QueryMatchTypeWithVariant'. NOTE; this is not manually written code, this is how the transfer sets up the view for us (directly from the 'Google Ads - Preview' transfer)!

I've tried changing the query that sets up the 'SearchQueryStats' view so that the name in the second call of 'QueryMatchTypeWithVariant' is changed to 'QueryMatchTypeWithVariant_2'. This fixes the issue for a day, making me able to query the view. Though the transfer seems to overwrite the changed query the next day, after which the same error occurs, making me unable to work with this table using scheduled queries. As @fazzig mentioned, excluding the columns from the scheduled query does sadly not help rid of the problem!

I've checked the documentation you've suggested, but have not found a fix yet!

[Snoshone07]

Hi @fazzig It seems like you're running into a common issue with duplicated columns in your BigQuery table, specifically with the QueryMatchTypeWithVariant field. This can happen due to how BigQuery handles schema changes, particularly when new columns are added or when there's a conflict during the import process.

Here are a few steps you can take to resolve this issue:

1. Check the Schema: Double-check the schema in BigQuery to confirm if there are indeed two columns with similar names (QueryMatchTypeWithVariant and QueryMatchTypeWithVariant_1). This might have occurred due to a schema update or an error during data ingestion.

2. Use Aliases: When querying the data, try using aliases to explicitly select the columns you need, which might help in bypassing the duplicated column issue. For example:

   SELECT campaignID, creativeID, QueryMatchTypeWithVariant AS QueryMatchType FROM `database.SearchQueryStats` LIMIT 1000;

   This can sometimes help if the issue is related to how BigQuery processes the columns.

3. Create a New Table/View: If the problem persists, you might want to create a new table or view without the duplicated column. This can be done by selecting the necessary columns and excluding the one causing issues:

   CREATE OR REPLACE TABLE `database.CleanedSearchQueryStats` AS SELECT campaignID, creativeID, otherColumns... FROM `database.SearchQueryStats` WHERE QueryMatchTypeWithVariant IS NOT NULL;

4. Contact Google Support: If none of the above solutions work, it might be necessary to reach out to Google Support. They can provide specific guidance or investigate if there's an underlying issue with the data transfer process.

Additionally, if you continue to face issues with managing your Google Ads data in BigQuery, you might want to consider exploring other tools or connectors that offer more flexibility in handling schema changes. Windsor.ai, for instance, has a Google Ads connector that could help ensure more consistent data integration without running into such issues.

Hope this helps!