01-06-2025 08:51 AM
I was able to connect our Bigquery account to our Databricks catalog. However, all the keys in the nested dictionary columsn populate as 'v'. For example:
{"v":[{"v":{"f":[{"v":"engagement_time_msec"},{"v":{"f":[{"v":null},{"v":"2"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"engaged_session_event"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_location"},{"v":{"f":[{"v":"https://www.logos.com/cart"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ignore_referrer"},{"v":{"f":[{"v":"true"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"batch_page_id"},{"v":{"f":[{"v":null},{"v":"1735751131326"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"batch_ordering_id"},{"v":{"f":[{"v":null},{"v":"3"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"currency"},{"v":{"f":[{"v":"USD"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_referrer"},{"v":{"f":[{"v":"https://www/subscriber"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_title"},{"v":{"f":[{"v":"Shopping Cart | Logos Bible Software"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"value"},{"v":{"f":[{"v":null},{"v":null},{"v":null},{"v":"74.87"}]}}]}},{"v":{"f":[{"v":"ga_session_id"},{"v":{"f":[{"v":null},{"v":"1735745404"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"session_engaged"},{"v":{"f":[{"v":"1"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_number"},{"v":{"f":[{"v":null},{"v":"6"},{"v":null},{"v":null}]}}]}}]}
01-07-2025 12:32 AM
Sorry @KristiLogos , I didn't notice that you're using lakehouse federation. In my case the extraction part was done in ADF, so I was able to use bigquery TO_JSON_STRING function to resolve this issue.
You can also take a look at below discussion and try to use autoflatten_optimised function.
Or take similar approach as @AnaMocanu in that thread.
Solved: Best way to parse Google Analytics data in Databri... - Databricks Community - 66976
01-06-2025 08:58 AM
Did you use any specific guide or code you can share with me to make this connection?
01-06-2025 09:32 AM
@Walter_C - I've pretty much connected via unity catalog by adding BigQuery as a new connection. I was asked and added my json key from Google Console for the specific BigQuery project when creating the connection:
https://docs.databricks.com/en/query-federation/bigquery.html
01-06-2025 12:10 PM - edited 01-06-2025 12:11 PM
Hi @KristiLogos ,
I had the same problem as you, but in my case I was extracting ga4 data from big query using ADF.
Try to use following code for every column that has JSON content.
select TO_JSON_STRING(items) as jsondata FROM ...
The result will contain the proper names instead of the "v" names.
01-06-2025 02:02 PM
@szymon_dybczak I couldn't run select TO_JSON_STRING(event_params) as event_params FROM ...
I don't think thats a built-in Databricks. Is there another way you've had success?
error:
[UNRESOLVED_ROUTINE] Cannot resolve routine `TO_JSON_STRING` on search path
01-07-2025 12:32 AM
Sorry @KristiLogos , I didn't notice that you're using lakehouse federation. In my case the extraction part was done in ADF, so I was able to use bigquery TO_JSON_STRING function to resolve this issue.
You can also take a look at below discussion and try to use autoflatten_optimised function.
Or take similar approach as @AnaMocanu in that thread.
Solved: Best way to parse Google Analytics data in Databri... - Databricks Community - 66976
01-07-2025 07:42 AM
@szymon_dybczak No worries, and thanks I'll try that out. Also, was there a reason in particular you went with ADF and not federated connection to BQ? Just curious
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group