LinkedIn
Twitter
Thank you so much again, @ms4446 Does this mean delete , insert statements are not required in the scheduled query?
-- delete
-- `my_project_id.ga4_master.a_table_name`
-- where
-- event_date = FORMAT_DATE('%Y%m%d', DATE_SUB(@run_date, INTERVAL 1 DAY)); --date_sub(@run_date, interval 1 day);
-- insert into
-- `my_project_id.ga4_master.a_table_name`
SELECT
event_date,
event_timestamp,
event_name,
event_bundle_sequence_id,
user_id,
user_pseudo_id,
...
FROM ...
WHERE...
and the destination table id sets to events_{run_time-24h|"%Y%m%d"} ?
i'm not sure what this 2 posts trying to achieve? in https://www.teamsimmer.com/2022/12/07/how-do-i-trigger-a-scheduled-query-when-the-ga4-daily-export-h...
My current log sink query when GA4 raw data ready:
resource.type="bigquery_dataset"
resource.labels.dataset_id="analytics_123456789"
resource.labels.project_id="my_project_id"
protoPayload.metadata.tableCreation.reason="JOB"
protoPayload.authenticationInfo.principalEmail="firebase-measurement@system.gserviceaccount.com"
protoPayload.methodName="google.cloud.bigquery.v2.JobService.InsertJob"
protoPayload.resourceName:"tables/events_"
NOT
protoPayload.resourceName:"tables/events_intraday"scheduled query: remove delete and insert statements
-- delete
-- `my_project_id.ga4_master.a_table_name`
-- where
-- event_date = FORMAT_DATE('%Y%m%d', DATE_SUB(@run_date, INTERVAL 1 DAY)); --date_sub(@run_date, interval 1 day);
-- insert into
-- `my_project_id.ga4_master.a_table_name`
SELECT
event_date,
event_timestamp,
event_name,
event_bundle_sequence_id,
user_id,
user_pseudo_id,
CONCAT((SELECT ep.value.int_value AS ga_session_id FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id'), user_pseudo_id) AS session_id,and my cloud function is now
const bigqueryDataTransfer = require('@google-cloud/bigquery-data-transfer');
exports.runScheduledQuery = async (event, context) => {
// Update configuration options
const projectId = 'my_project_id';
const configId = 'the id from scheduled query';
const region = 'eu';
console.log('Received event:', JSON.stringify(event));
// Modify the log data structure if needed
const protoPayload = {
methodName: "google.cloud.bigquery.v2.JobService.InsertJob",
jobCompletedEvent: {
job: {
jobConfiguration: {
load: {
destinationTable: {
datasetId: "my_project_id.ga4_master",
tableId: "~^events_\\d+"
}
}
}
}
}
};
const destinationTableId = protoPayload.jobCompletedEvent.job.jobConfiguration.load.destinationTable.tableId;
// Grab the table date and turn it into the run time for the scheduled query
const tableTime = destinationTableId.replace('events_', '');
const year = tableTime.substring(0, 4),
month = tableTime.substring(4, 6),
day = tableTime.substring(6, 8);
// Set the run time for the day after the table date so that the scheduled query works with "yesterday's" data
const runTime = new Date(Date.UTC(year, month - 1, parseInt(day) + 1, 12));
// Create a proto-buffer Timestamp object from this
const requestedRunTime = bigqueryDataTransfer.protos.google.protobuf.Timestamp.fromObject({
seconds: runTime / 1000,
nanos: (runTime % 1000) * 1e6
});
const client = new bigqueryDataTransfer.v1.DataTransferServiceClient();
const parent = client.projectLocationTransferConfigPath(projectId, region, configId);
const request = {
parent,
requestedRunTime
};
const response = await client.startManualTransferRuns(request);
return response;
};I have to wait until the next ga4 raw event table created and if the function works at my time which is tomorrow. Thanks for your time ms4446.
It looks like the cloud function is working. However, i got an error in scheduled query history:
I removed the partitioning field in scheduled query setting and retry the query. The table events_19691231 was created. I think i need to specify the partitioning field to a field which has type TIMESTAMP, DATETIME or DATE but not string.
Here's the GA4 raw data tables
The destination table in ga4_master dataset should be events_20231020. How do i fix it? thanks
Finally, the issue has been fixed. Here's the code i used in function.
const bigqueryDataTransfer = require('@google-cloud/bigquery-data-transfer');
exports.runScheduledQuery = async (event, context) => {
// Update configuration options
const projectId = 'my_project_id';
const configId = 'the_scheduled_query_id';
const region = 'eu';
console.log('Received event:', JSON.stringify(event));
// Use the modified log data structure
// Load the log data from the buffer
const eventData = JSON.parse(Buffer.from(event.data, 'base64').toString());
const destinationTableId = protoPayload.jobCompletedEvent.job.jobConfiguration.load.destinationTable.tableId;
const tableName = eventData.protoPayload.metadata.tableCreation.table.tableName;
const event_tableName = tableName.slice(63, 78);
console.log(event_tableName);
// Grab the table date and turn it into the run time for the scheduled query
const tableTime = event_tableName.replace('events_', '');
const year = tableTime.substring(0, 4),
month = tableTime.substring(4, 6),
day = tableTime.substring(6, 8);
// Set the run time for the day after the table date so that the scheduled query works with "yesterday's" data
const runTime = new Date(Date.UTC(year, month - 1, parseInt(day) + 1, 12));
// Create a proto-buffer Timestamp object from this
const requestedRunTime = bigqueryDataTransfer.protos.google.protobuf.Timestamp.fromObject({
seconds: runTime / 1000,
nanos: (runTime % 1000) * 1e6
});
const client = new bigqueryDataTransfer.v1.DataTransferServiceClient();
const parent = client.projectLocationTransferConfigPath(projectId, region, configId);
const request = {
parent,
requestedRunTime
};
const response = await client.startManualTransferRuns(request);
return response;
};
The result in ga4_master dataset is showing the correct table name events_20231022 and both contents of ga4 raw data and new scheduled query results are match.
