DB

How to convert BigQuery column to array

How can I convert column to array in BqgQuery ?

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.


BQ has one useful data format array.

It can handle multiple data like [1,2,3] in 1 row.

But how can we create array data from column data.

So today I introduce about "How to convert BigQuery column to array".

Author


Mid-carieer engineer (AI, system). Good at Python and SQL.

Advantage to read

You can understand "How to convert BigQuery column to array". Then you don't have to concern about array data.


Data

First, we have to prepare data.

Import this CSV data below as table array_sample.

key,val
a,1
a,2
a,3
b,2
b,4
b,5
c,3
c,2
c,-1


So 2-column-table would be created.



How to convert BigQuery column to array

how

In order to convert BigQuery column to array, we can use ARRAY_AGG().

ARRAY_AGG() is aggregate function that returns array.

You can use this function like below.

SQL

SELECT
key,
ARRAY_AGG(val) as val_array
FROM test.array_sample
GROUP BY key


This SQL returns result below.

Result

Row key val_array
1 a 1
2
3
2 b 2
4
5
3 c 3
2
-1


Per key, values are aggregated to array.




Conclusion

Today I explained about "How to convert BigQuery column to array".

In order to convert column to array in BigQuery, we can take this solution.

Point

Use aggregate function ARRAY_AGG().

It is easy to understand because we can use GROUP BY clause.


BigQuery Book


There are some other articles about BigQuey.

If you interested in them, please read them.

If you felt this article is useful, please share.

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

© 2022 ITips