Azure Data Factory Copy Data error when exporting from BigQuery

I need to export Google Analytics data from the "events" dataset stored on BigQuery.

I'm having ad issue on the Copy Data activity when trying to export data,
the error I'm receiving is the following:

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][BigQuery] (310) Storage API Error: Error thrown in GAvroDataCell:BuildDefaultAvroCell.Invalid BigQueryType specified for copy/move operation. Type is : 0,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][BigQuery] (310) Storage API Error: Error thrown in GAvroDataCell:BuildDefaultAvroCell.Invalid BigQueryType specified for copy/move operation. Type is : 0,Source=Microsoft ODBC Driver for Google BigQuery,'

The pipeline is made of a single Copy Data activity and this is the query I'm executing:

	@concat('SELECT
	  event_date,
	  event_timestamp,
	  event_name,
	  user_id,
	  user_pseudo_id,
	  TO_JSON(event_params) as event_params,
	  event_previous_timestamp,
	  event_value_in_usd,
	  event_bundle_sequence_id,
	  event_server_timestamp_offset,
	  TO_JSON(privacy_info) as privacy_info,
	  TO_JSON(user_properties) as user_properties,
	  user_first_touch_timestamp,
	  TO_JSON(user_ltv) as user_ltv,
	  TO_JSON(device) as device,
	  TO_JSON(geo) as geo,
	  TO_JSON(app_info) as app_info,
	  TO_JSON(traffic_source) as traffic_source,
	  TO_JSON(stream_id) as stream_id,
	  TO_JSON(platform) as platform,
	  TO_JSON(event_dimensions) as event_dimensions,
	  TO_JSON(ecommerce) as ecommerce,
	  TO_JSON(items) as items
	FROM `xxxxxxxproject-bigquery.analytics_310106672.events_2023*`'
	)

The linked service of type "BigQuery" is working since the "test connection" results with "ok", and also the dataset is ok because "preview data" returns data from the remote dataset.

The "preview data" on the Copy Data activity gives error like this:

enter image description here

I've found that it has something to do with the widlcard "*" in the tablename. The copy data works when I construct a dynamic query like this:

@concat('SELECT <fields>
FROM `xxxxxxxproject-bigquery.analytics_310106672.events_', '20230520`')

But this one is not working:

@concat('SELECT <fields>
FROM `xxxxxxxproject-bigquery.analytics_310106672.events_', '202305', '*', '`')

I'm investigating on google source side.

AFAIK, Currently ADF might not support the wildcard table selection like Big Query queries and that is the reason for your error.

The workaround for your requirement can be list out all the tables from the source using the below query with a Lookup activity.

SELECT table_name FROM `<project>`.<dataset_id>.INFORMATION_SCHEMA.TABLES;

If you want, you can filter this output array with starts_with() using filter activity.

Then Give this output array to a ForEach activity.

Inside ForEach, use copy activity and give @item().table_name for both source dataset and sink dataset(use dataset parameters here).

If you are copying data to ADLS copy each table to a temporary table of a folder and after ForEach, use another copy activity and use wild card file path in the source. In the sink of the copy activity, use merge copy type and these files will be saved to single file.