In Google BigQuery I have a column in a table which contains JSON objects similar to this one select JSON '[{"id":"A","value":"1"},{"id":"B","value":"2"},{"id":"C","value":"John"},{"id":"D","value":null},{"id":"E","value":"random"}]' as JSON_ I want to create a new column with the value of the JSON object where the id…
I have data arriving as separate events in JSON form resembling: { "id":1234, "data":{ "packet1":{"name":"packet1", "value":1}, "packet2":{"name":"packet2", "value":2} } } I'd like to unnest the data to essentially have one row per 'packet' (there may be any number of packets).…
I have a field, called created_by in a bigquery table, with text like this: created by "{"name": "Charles Darwin", "email": "[email protected]"}" The field is a "STRING" type field. What I need is to extract the name and the email in…
I have the firebase extension for streaming data to Big Query installed https://extensions.dev/extensions/firebase/firestore-bigquery-export. Each month I run a job to import data into my Firestore collection in batches. This month I imported 2706 rows but only 2646 made it into…
we are trying to read a table from Bigquery to spark dataframe. Strucute of the table is Following pyspark code is used for reading the data. from google.oauth2 import service_account from google.cloud import bigquery import json import base64 as bs…
I want to merge JSON string in a table and sum its value after group for Eg:- 2023, {"hen":4, "owl":3} 2023, {"crow":4, "owl":2} 2022, {"owl":6, "crow":2} 2022, {"hen":5} 2021, {"hen":2, "crow":1} Result could be like below 2023, {"hen":4, "owl":5, "crow":4}…
I am using Twillio Segment to sync web_app_ui events into bigquery (serves as data lake). In Bigquery data we have many tables with similar structure. Most fields are common, but some are different. We have many such tables (~200), and…
I want to load data from MySQL to BigQuery using Cloud Dataflow. Anyone can share article or work experience about load data from MySQL to BigQuery using Cloud Dataflow with Python language? Thank you
I need to import from G-Analytics to Amplitude the historical data. The amplitude api show a data example of how to prepare the dato to ulpload to his site. I need to export from GA each event ever recorded this…
Run into sql code with a lot of when: case when callDuration > 0 and callDuration < 30 then 1.4 when callDuration >= 30 and callDuration < 60 then 2.3 when callDuration >= 60 and callDuration < 120 then 3.7…