Add a source BigQuery

[awoehrl]

Requires #4401

Tell us about the new integration you’d like to have

Which source and which destination? Which frequency?
I'm looking into a way to get data out of BigQuery and into a postgres database.

Describe the context around this new integration

This is important for our internal APIs that are feed by a postgres database. The raw data lies in BigQuery and the aggregates need to go to postgres.

Describe the alternative you are considering or using

I will have to setup some messy cloud functions to get the data out or setup a meltano instance.

┆Issue is synchronized with this Asana task by Unito

[sherifnada]

@awoehrl Thanks for creating the issue! what scale of data would you want to sync with Airbyte?

[awoehrl]

My main use case would be building APIs based on BigQuery data. One example is a content recommender API based on web analytics data that sits in BigQuery. The amount of data that I’m querying for that is around 50 GB every 30 Minutes. Right now I’m setting up the process like this: 1. Scheduled queries build the tables 2. A cloud function moves the data from BigQuery to GCS 3. A cloud function moves the data to Cloud SQL Postgres. From: Sherif A. Nada <notifications@github.com> Sent: Freitag, 5. Februar 2021 19:37 To: airbytehq/airbyte <airbyte@noreply.github.com> Cc: Wöhrl Andreas <Andreas.Woehrl@mittelbayerische.de>; Mention <mention@noreply.github.com> Subject: Re: [airbytehq/airbyte] Add a source BigQuery (#1876) @awoehrl<https://github.com/awoehrl> what scale of data would you want to sync with Airbyte? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#1876 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKWL52FF4WRHDW5AZU37WBLS5Q3DRANCNFSM4WWXKBWQ>.

[sherifnada]

A user (@erosen3 ) has expressed interest in being able to sync views from BigQuery as well. It would be nice to have this feature

[edbizarro]

👍

Our use case is to sync some tables/datasets between GCP projects, like tables exported by Google Analytics 360 and Firebase to bigquery, about to 2-5TB/day.

The native functionality in BigQuery (BQ Transfer Service) for this purpose don't fully met our need because they have a minimum frequency of 12 hours and we need at least 6 hours for this sync interval.

[DoNotPanicUA]

The latest state of the comprehensive tests after #4981

Data Type	Insert values	Expected values	Comment	Common test result
int64	null, -128, 127, 9223372036854775807, -9223372036854775808	null, -128, 127, 9223372036854775807, -9223372036854775808		Ok
int	null, -128, 127	null, -128, 127		Ok
smallint	null, -128, 127	null, -128, 127		Ok
integer	null, -128, 127	null, -128, 127		Ok
bigint	null, -128, 127	null, -128, 127		Ok
tinyint	null, -128, 127	null, -128, 127		Ok
byteint	null, -128, 127	null, -128, 127		Ok
numeric	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789		Ok
bignumeric	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789		Ok
decimal	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789		Ok
bigdecimal	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789	null, -128, 127, 999999999999999999, -999999999999999999, 0.123456789, -0.123456789		Ok
float64	null, -128, 127, 0.123456789, -0.123456789	null, -128.0, 127.0, 0.123456789, -0.123456789		Ok
bool	true, false, null	true, false, null		Ok
bytes	FROM_BASE64("test"), null	test, null		Ok
date	date('2021-10-20'), date('9999-12-31'), date('0001-01-01'), null	2021-10-20T00:00:00Z, 9999-12-31T00:00:00Z, 0001-01-01T00:00:00Z, null		Ok
datetime	datetime('2021-10-20 11:22:33'), datetime('9999-12-31 11:22:33'), datetime('0001-01-01 11:22:33'), null	2021-10-20T11:22:33Z, 9999-12-31T11:22:33Z, 0001-01-01T11:22:33Z, null		Ok
timestamp	timestamp('2021-10-20 11:22:33'), null	2021-10-20T11:22:33Z, null		Ok
geography	ST_GEOGFROMTEXT('POINT(1 2)'), null	POINT(1 2), null		Ok
string	'qwe', 'йцу', null	qwe, йцу, null		Ok
struct	STRUCT("B.A",12), null			Ok
time	TIME(15, 30, 00), null	15:30:00, null		Ok
array	['a', 'b']	[{"test_column":"a"},{"test_column":"b"}]		Ok
struct	STRUCT('s' as frst, 1 as sec, STRUCT(555 as id_col, STRUCT(TIME(15, 30, 00) as time) as mega_obbj) as obbj)	{"frst":"s","sec":1,"obbj":{"id_col":555,"mega_obbj":{"last_col":"15:30:00"}}}		Ok
array	[STRUCT('qqq' as fff, 1 as ggg), STRUCT('kkk' as fff, 2 as ggg)]	[{"fff":"qqq","ggg":1},{"fff":"kkk","ggg":2}]		Ok
array	[STRUCT('qqq' as fff, [STRUCT('fff' as ooo, 1 as kkk), STRUCT('hhh' as ooo, 2 as kkk)] as ggg)]	[{"fff":"qqq","ggg":[{"ooo":"fff","kkk":1},{"ooo":"hhh","kkk":2}]}]		Ok