Inserting and updating data with BigQuery's API

Question

I'm trying to insert and update data from a mobile app every time an user opens an app and logs into Facebook.

Is it possible to insert and update specific rows from a table using BigQuery's API ? If not, is there an alternative solution ?

Thanks in advance!

Mikhail Berlyant · Accepted Answer · 2017-04-12 14:49:11Z

3

You can use Job.insert API to issue DML query to UPDATE specific row(s)

Please note: BigQuery is not designed for transactions - so read carefully about quotas and pricing

answered Apr 12, 2017 at 14:49

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

azekirel555 Over a year ago

Thanks for your answer @Mikhail Berlyant, as it looks like what I'm looking to do isn't the best solution for my situation. How would you advise me to proceed considering there would possibly be thousands of rows of data to be inserted each second? Do you think sending requests containing the data to insert to the API from each mobile device is the most appropriate thing to do here ? I thought of using a version / date column so it wouldn't be necessary to use updates. Also, what do you think of date-partitioned table there? Thanks again!

Mikhail Berlyant Over a year ago

as I mentioned - BigQuery is not tailored for transactional data, so updates are not what i can recommend - I referenced quotas and price links - they tells the story. in your case you should look into streaming API - cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/…

azekirel555 Over a year ago

Thanks for your reply @Mikhail Berlyant. Are you sure I can use the streaming API directly from the mobile app? From what I've read, to use the streaming API, I need to create a BigQuery client, and to use a BQ client, I need to authenticate. In that case, I think I might have to create an API that will receive the data from the devices that would open the mobile app to stream the data to BQ. I might be missing something.

Mikhail Berlyant Over a year ago

@azekirel555 - that's to broad and definitely new question that is no way to answer in comments. I recommend you to post new specific question with all details you have

Jamilah Foucher · Accepted Answer · 2023-12-07 17:40:51Z

1

I used jobs.query (https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query), it was less complicated than jobs.insert. You can use a normal BigQuery SQL query directly under the POST json key "query".

projectId = "PROJECT_ID";
{
"query": "INSERT INTO DATASET_NAME.TABLE_NAME (COLUMN_NAME0, COLUMN_NAME1) VALUES('value0_here', 'value1_here');",
  "location": "LOCATION",
  "useLegacySql": false
}

answered Dec 7, 2023 at 17:40

Jamilah Foucher

214 bronze badges

Collectives™ on Stack Overflow

Inserting and updating data with BigQuery's API

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related