topic Re: How to create connection between Databricks & BigQuery in Data Engineering

How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 09:42:12 GMT

Hi,

I would like to connect our BigQuery env to Databricks, So I created a service account but where should I configure the service account in Databricks? I read databricks documention and it`s not clear at all.

Thanks for your help

Re: How to create connection between Databricks & BigQuery

-werners- — Thu, 01 Dec 2022 11:17:54 GMT

https://docs.databricks.com/external-data/bigquery.html

Can you elaborate what is not clear?

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 11:21:07 GMT

yeah, part number 2 - setup Databricks, there is the below code

credentials <base64-keys>

spark.hadoop.google.cloud.auth.service.account.enable true

spark.hadoop.fs.gs.auth.service.account.email <client_email>

spark.hadoop.fs.gs.project.id <project_id>

spark.hadoop.fs.gs.auth.service.account.private.key <private_key>

spark.hadoop.fs.gs.auth.service.account.private.key.id <private_key_id>

what should it replace instead of <base64-keys> ? the google service account key (json) ? if yes what part of it ?

Re: How to create connection between Databricks & BigQuery

mcwir — Thu, 01 Dec 2022 11:21:20 GMT

Here are the docs : https://docs.databricks.com/external-data/bigquery.html?_ga=2.254305484.510683761.1669885489-463474086.1669885489

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 11:23:29 GMT

I familar with this doc, it is not clear (please find my previous comment)

Re: How to create connection between Databricks & BigQuery

-werners- — Thu, 01 Dec 2022 11:24:27 GMT

the base64-keys is generated from the json key file:

To configure a cluster to access BigQuery tables, you must provide your JSON key file as a Spark configuration. Use a local tool to Base64-encode your JSON key file. For security purposes do not use a web-based or remote tool that could access your keys.

The JSON key file is created right above the following section:

https://docs.databricks.com/external-data/bigquery.html#create-a-google-cloud-storage-gcs-bucket-for-temporary-storage

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 11:35:22 GMT

So basically it should look like this :

credentials <adfasdfsadfadsfsdafsd>

spark.hadoop.google.cloud.auth.service.account.enable true

spark.hadoop.fs.gs.auth.service.account.email <user@service.com>

spark.hadoop.fs.gs.project.id <project-dd>

spark.hadoop.fs.gs.auth.service.account.private.key <fdsfsdfsdgfd>

spark.hadoop.fs.gs.auth.service.account.private.key.id <gsdfgsdgdsg>

? Do I need to add "" ?

Re: How to create connection between Databricks & BigQuery

-werners- — Thu, 01 Dec 2022 11:46:22 GMT

without the pointy brackets. they are placeholders for values.

so unless you want to enter a variable which you already declared (like credentials in your example), put the double quotes.

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 12:17:42 GMT

Thanks werners.

it now working, when I'm runnning the below script:

df = spark.read.format("bigquery").option("table","sandbox.test").load()

im getting the below error:

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 12:18:00 GMT

Re: How to create connection between Databricks & BigQuery

-werners- — Thu, 01 Dec 2022 12:25:25 GMT

are you sure the path to the table is correct?

the example is a bit different:

"bigquery-public-data.samples.shakespeare"

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 12:33:47 GMT

I also changed the path to "test_proj.sandbox.test".

the error is :

A project ID is required for this service but could not be determined from the builder or the environment. Please set a project ID using the builder.

Re: How to create connection between Databricks & BigQuery

-werners- — Thu, 01 Dec 2022 12:38:05 GMT

I guess something still has to be configured on BigQuery.

can you check this thread?

https://github.com/GoogleCloudDataproc/spark-bigquery-connector/issues/40

Re: How to create connection between Databricks & BigQuery

519776 — Thu, 01 Dec 2022 12:43:23 GMT

Works 🙂

Thanks werners, many thanks .

Re: How to create connection between Databricks & BigQuery

karthik_p — Thu, 01 Dec 2022 21:24:09 GMT

@kfiry adding to @Werner Stinckens did you added projectid in read spark query , projectid should be one where big query instance running. also please follow best practices in terms of egress data cost

spark.read.format("bigquery") \

.option("table", table) \

.option("project", <project-id>) \

.option("parentProject", <parent-project-id>) \

.load()

Re: How to create connection between Databricks & BigQuery

308655 — Fri, 03 Feb 2023 03:53:04 GMT

Thank you. For me, setting parent project ID solved it. This is also in the documentation

spark.read.format("bigquery") \

.option("table", table) \

.option("project", <project-id>) \

.option("parentProject", <parent-project-id>) \

.load()

I didn't have to set the various spark.hadoop.fs.gs config variables for the cluster, as it seemed content with the base64 credentials.