How can I add a Bigquery record, with autoincrement ID column?

Queries and Resources
1

I have a table in BigQuery which will recieve new rows input by the user. One of the columns of this table is the ID, how can I make sure this ID is an autoincremented value?

2

Hey @Johan,

There's no auto increment functionality for a PK in BQ tables unless you want a UUID which you can generate one using JS query. What you can do is specify that one on your schema and also in your process of inserting a row. By process of inserting a row, I mean reading the max ID of your table (1 resource -- read) -> insert the new row with an increment for that max value (1 resource -- write).

Not sure if this is the process you intended, otherwise you can check this surrogate keys and how Google consider this as practical approach.

I'm not sure what's the purpose of doing this in BQ but I don't think it is practical to use BQ as your transactional database. It's meant to be a data warehouse and for you to query large dataset. You can use a cloud SQL or cloud storage for csv files. These sources can be connected to BQ directly.

1 Like
3

I have resorted to using JS in the ID variable, not sure how sustainable it will be, but each element which requires the autoincrement will have at most 10-25 new ids to them.
The way I set the id column to be populated was using the following code:

{{isNaN(Math.max(element.data.id)) ? 0 : parseInt(Math.max(element.data.id))+1}}

And yes you're right, it's not supposed to be a transactional DB, I will eventually move this to a cloud sql instance, but for now one of the datasets from my BQ will deal with updates from the retool app.
Thanks!

1 Like
4

Hm my approach is actually not working it seems, Im not sure but the {{Math.max(element.data.id)}} is always outputting NaN.

shouldnt the max function get the max value in the array of ID's?

5

Hi @Johan, you might find the lodash _.max() method helpful when working with an array.

1358×706 43.7 KB

Let us know if that works!

6