Skip to main content
Solved

Ingestion error while importing data from BIgquery: No user_id or device_id found

  • June 12, 2023
  • 1 reply
  • 311 views

Hi everyone,

I have been trying to ingest our Bigquery data to Amplitude. But I have been facing a very weird problem. In some of our tables, We have `anonymous_id`  with a value `anonymous` with string data type and I use this value in place of `user_id` in ingest_config sql query but I get ingestion error with `no user_id or device_id found in the events` whereas in  another table, I do the same with same `anonymous_id` replacing `user_id` but all the data of the table have been successfully ingested into Amplitude. I am not able to understand the difference?

Attaching the ingest_config query here for both the `table1` and `table2` respectively:

SELECT
  event AS event_type,
  STRUCT(event_text as event, event_status_flag as event_status_flag, context_page_url as context_page_url)AS event_properties, -- must be JSON
  UNIX_MILLIS(original_timestamp) AS time, -- event time, must be in milliseconds since epoch (timestamp), e.g. UNIX_MILLIS(timestamp)
  loaded_at, -- Timestamp column required for time based import only, usually server upload time or database insertion time, must be in TIMESTAMP
  anonymous_id as user_id
FROM `vibi-prod.team_monitor_website.download_page_call`

 Query for table 1 with ingestion error and the error is: {"offset":0,"error":"events must have either user_id or device_id or both (Missing device or user id): 

SELECT
  name AS event_type,
  STRUCT(path as page_path,
  url as page_url,
  name as page_name) AS event_properties, -- must be JSON
  UNIX_MILLIS(original_timestamp) AS time, -- event time, must be in milliseconds since epoch (timestamp), e.g. UNIX_MILLIS(timestamp)
  loaded_at, -- Timestamp column required for time based import only, usually server upload time or database insertion time, must be in TIMESTAMP
  anonymous_id as user_id
FROM `vibi-prod.team_monitor_website.pages`

Query for table2 where I get no ingestion error. But what I can guess is in the above `Table1` `anonymous_id` value is `anonymous` wherese in `table2` the value of `anonymous_id` is generated using `uuidv4()` method.

 

So, my question is, does Amplitude only accepts user_id value is `02c041ca-cf5b-40c0-a214-95bb4b2ebc7f` such format only? 

Best answer by Saish Redkar

Hi @Muskan Paliwal 

That’s the correct interpretation regarding acceptable values of the user_id field.

Here’s more on the what’s accepted as user_ids : https://www.docs.developers.amplitude.com/analytics/apis/http-v2-api/?h=user+id#device-ids-and-user-ids-minimum-length
 

Hope this helps.

View original
Did this topic help you find an answer to your question?

1 reply

Saish Redkar
Expert
Forum|alt.badge.img+10
  • Expert
  • 1382 replies
  • Answer
  • June 13, 2023

Hi @Muskan Paliwal 

That’s the correct interpretation regarding acceptable values of the user_id field.

Here’s more on the what’s accepted as user_ids : https://www.docs.developers.amplitude.com/analytics/apis/http-v2-api/?h=user+id#device-ids-and-user-ids-minimum-length
 

Hope this helps.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings