idea From now on, please track this idea from the Ideation por... in Suggest an Idea

Big Query endpoint: Create Streaming Capabilities

joseph_jbh — Thu, 27 Oct 2022 12:40:00 GMT

Google is telling us that the current Qlik implementation of its Big Query endpoint uses “micro-batching” to get data into Big Query. While that does work, it does run afoul of a couple of quotas, limiting the number of batches a day, and the number of tables/partitions in a project.

Can you create another version of the endpoint that utilizes their latest streaming ingestion, using their new Write API? Here are a couple of references:

https://cloud.google.com/bigquery/docs/write-api

BigQuery Write API explained: An overview of the Write API | Google Cloud Blog

Some of the advantages:

Exactly-once delivery semantics. The Storage Write API supports exactly-once semantics through the use of stream offsets. Unlike the tabledata.insertAll method, the Storage Write API never writes two messages that have the same offset within a stream, if the client provides stream offsets when appending records.

Stream-level transactions. You can write data to a stream and commit the data as a single transaction. If the commit operation fails, you can safely retry the operation.

Transactions across streams. Multiple workers can create their own streams to process data independently. When all the workers have finished, you can commit all of the streams as a transaction.

Efficient protocol. The Storage Write API is more efficient than the older insertAll method because it uses gRPC streaming rather than REST over HTTP. The Storage Write API also supports binary formats in the form of protocol buffers, which are a more efficient wire format than JSON. Write requests are asynchronous with guaranteed ordering.

Schema update detection. If the underlying table schema changes while the client is streaming, then the Storage Write API notifies the client. The client can decide whether to reconnect using the updated schema, or continue to write to the existing connection.

Lower cost. The Storage Write API has a significantly lower cost than the older insertAll streaming API. In addition, you can ingest up to 2 TB per month for free.

Thank you!

Re: Big Query endpoint: Create Streaming Capabilities

Bayu — Tue, 29 Nov 2022 18:59:56 GMT

In our POC with google, we're using qlik replicate to push to kafka into 2 different topics. One topic is to store the cdc records and the other is to store schema changes. The format of the data is stored in json format. From there we have a Dataflow (google manage product of apache beam) that read the json format and insert data to BQ table. Again the purpose of the Dataflow is to reads cdc records from Kafka (json format) and reformat and appends records to BQ tables.

In this ideation, we're looking if Qlik can create a new end point for BQ where it can remove the need for Kafka and Dataflow. The process itself only involves append process (no need to actually apply insert/update/delete in BQ). Once the data is stored chronologically in BQ, my team can build a view that will make the user see only the latest data.

Currently the Dataflow is also creating the view... so ideally we would like Qlik also handles the view creation.

Re: Big Query endpoint: Create Streaming Capabilities

bobvecchione — Thu, 02 Feb 2023 18:33:11 GMT

Hey Joe - We do in fact have this request on our radar for Replicate and our Qlik Cloud DI. It is a relatively high priority, but we have not started the work yet.

You can always ping me directly (you have my email) 🙂 and I will keep you up to date.

Thanks

--bobv--

Re: Big Query endpoint: Create Streaming Capabilities

joseph_jbh — Thu, 02 Feb 2023 19:17:18 GMT

Excellent news....Thanks for the update, @bobvecchione !

Re: Big Query endpoint: Create Streaming Capabilities

marina_marshak — Mon, 03 Apr 2023 18:18:17 GMT

Hello Joseph,

Wanted to update you that we did initial evaluation of this feature with the R&D and we have added it to our roadmap.

It's important item both for Replicate and Qlik cloud Data Integration, but it's not something we will be targeting in the short term, as this is not a straightforward implementation, and we are currently tied with other engagements.

Re: Big Query endpoint: Create Streaming Capabilities - Status changed to: Open - On Roadmap

marina_marshak — Mon, 03 Apr 2023 18:18:38 GMT

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 15:08:37 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Big Query endpoint: Create Streaming Capabilities - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:08:39 GMT