I have followed Use the BigQuery connector with Spark to successfully get data from a publicly available dataset. I now need to access a bigquery dataset that is owned by one of our clients and for which I have been given a service account key file (I know that the service account key file is valid because I can use it to connect using the Google BigQuery library for Python).
I have followed what Igor Dvorzhak recommended here
To use service account key file authorization you need to set
mapred.bq.auth.service.account.enableproperty to true and point BigQuery connector to a service account json keyfile usingmapred.bq.auth.service.account.json.keyfileproperty
like so:
from pyspark.sql import SparkSession
from datetime import datetime
spark = SparkSession.builder.appName("SparkSessionBQExample").enableHiveSupport().getOrCreate()
bucket = spark._jsc.hadoopConfiguration().get('fs.gs.system.bucket')
project = spark._jsc.hadoopConfiguration().get('fs.gs.project.id')
input_directory = 'gs://{}/hadoop/tmp/bigquery/pyspark_input{}'.format(bucket, datetime.now().strftime("%Y%m%d%H%M%S"))
project_id = 'clientproject'#'publicdata'
dataset_id = 'clientdataset'#samples'
table_id = 'clienttable'#'shakespeare'
conf = {
# Input Parameters.
'mapred.bq.project.id': project,
'mapred.bq.gcs.bucket': bucket,
'mapred.bq.temp.gcs.path': input_directory,
'mapred.bq.input.project.id': project_id,
'mapred.bq.input.dataset.id': dataset_id,
'mapred.bq.input.table.id': table_id,
'mapred.bq.auth.service.account.enable': 'true'
}
# Load data in from BigQuery.
table_data = spark.sparkContext.newAPIHadoopRDD(
'com.google.cloud.hadoop.io.bigquery.JsonTextBigQueryInputFormat',
'org.apache.hadoop.io.LongWritable',
'com.google.gson.JsonObject',
conf=conf)
print ('row tally={}'.format(table_data.toDF().count()))
I have placed the service account key file at /tmp/keyfile.json on the master node and all the worker nodes of the cluster then I submit my job like so:
gcloud dataproc jobs submit pyspark \
./bq_pyspark.py \
--cluster $CLUSTER \
--region $REGION \
--properties=spark.hadoop.mapred.bq.auth.service.account.json.keyfile=/tmp/keyfile.json
I have also tried:
gcloud dataproc jobs submit pyspark \
./bq_pyspark.py \
--cluster $CLUSTER \
--region $REGION \
--properties=spark.hadoop.mapred.bq.auth.service.account.json.keyfile=/tmp/keyfile.json,spark.hadoop.mapred.bq.auth.service.account.enable=true
Here are the pertinent sections of the job output:
Bigquery connector version 0.10.7-hadoop2
18/11/07 13:36:47 INFO com.google.cloud.hadoop.io.bigquery.BigQueryFactory: Creating BigQuery from default credential.
18/11/07 13:36:47 INFO com.google.cloud.hadoop.io.bigquery.BigQueryFactory: Creating BigQuery from given credential.
18/11/07 13:36:47 INFO com.google.cloud.hadoop.io.bigquery.BigQueryConfiguration: Using working path: 'gs://dataproc-9e5dc592-1a35-42e6-9dd6-5f9dd9c8df87-europe-west1/hadoop/tmp/bigquery/pyspark_input20181107133646'
Traceback (most recent call last):
File "/tmp/b6973a26c76d4069a86806dfbd2d7d0f/bq_pyspark.py", line 30, in
conf=conf)
File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/context.py", line 702, in newAPIHadoopRDD
File "/usr/lib/spark/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py", line 1133, in call
File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 63, in deco
File "/usr/lib/spark/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py", line 319, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.newAPIHadoopRDD.
: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
{
"code" : 403,
"errors" : [ {
"domain" : "global",
"message" : "Access Denied: Table clientproject:clientdatatset.clienttable: The user [email protected] does not have bigquery.tables.get permission for table clientproject:clientdatatset.clienttable.",
"reason" : "accessDenied"
} ],
"message" : "Access Denied: Table clientproject:clientdatatset.clienttable: The user [email protected] does not have bigquery.tables.get permission for table clientproject:clientdatatset.clienttable."
}
The line
18/11/07 13:36:47 INFO com.google.cloud.hadoop.io.bigquery.BigQueryFactory: Creating BigQuery from default credential.
possibly suggests that I'm not passing the credentials from the service account key file correctly so I guess I've misunderstood what Igor said (or some info is missing).
If anyone can let me know where I'm going wrong I'd very much appreciate it.
UPDATE... I have attempted to supply the required auth configuration via code instead of via the command-line:
conf = {
# Input Parameters.
'mapred.bq.project.id': project,
'mapred.bq.gcs.bucket': bucket,
'mapred.bq.temp.gcs.path': input_directory,
'mapred.bq.input.project.id': project_id,
'mapred.bq.input.dataset.id': dataset_id,
'mapred.bq.input.table.id': table_id,
'mapred.bq.auth.service.account.enable': 'true',
'mapred.bq.auth.service.account.keyfile': '/tmp/keyfile.json',
'mapred.bq.auth.service.account.email': '[email protected]'
}
This time I got a different error:
18/11/07 16:44:21 INFO com.google.cloud.hadoop.io.bigquery.BigQueryFactory: Creating BigQuery from default credential.
Traceback (most recent call last):
File "/tmp/cb5cbb16d59945dd926cab2c1f2f5524/bq_pyspark.py", line 39, in
conf=conf)
File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/context.py", line 702, in newAPIHadoopRDD
File "/usr/lib/spark/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py", line 1133, in call
File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 63, in deco
File "/usr/lib/spark/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py", line 319, in get_return_value py4j.protocol.Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.newAPIHadoopRDD.
: java.io.IOException: toDerInputStream rejects tag type 123
at sun.security.util.DerValue.toDerInputStream(DerValue.java:881)
at sun.security.pkcs12.PKCS12KeyStore.engineLoad(PKCS12KeyStore.java:1939)
at java.security.KeyStore.load(KeyStore.java:1445)
at com.google.api.client.util.SecurityUtils.loadKeyStore(SecurityUtils.java:82)
at com.google.api.client.util.SecurityUtils.loadPrivateKeyFromKeyStore(SecurityUtils.java:115)
at com.google.api.client.googleapis.auth.oauth2.GoogleCredential$Builder.setServiceAccountPrivateKeyFromP12File(GoogleCredential.java:670)
at com.google.cloud.hadoop.util.CredentialFactory.getCredentialFromPrivateKeyServiceAccount(CredentialFactory.java:251)
at com.google.cloud.hadoop.util.CredentialConfiguration.getCredential(CredentialConfiguration.java:100)
at com.google.cloud.hadoop.io.bigquery.BigQueryFactory.createBigQueryCredential(BigQueryFactory.java:95)
at com.google.cloud.hadoop.io.bigquery.BigQueryFactory.getBigQuery(BigQueryFactory.java:115)
at com.google.cloud.hadoop.io.bigquery.BigQueryFactory.getBigQueryHelper(BigQueryFactory.java:103)
I googled "toDerInputStream rejects tag type 123" which led me to toDerInputStream rejects tag type 123 which suggests I need to authenticate using a P12 file. This is consistent with the mention of sun.security.pkcs12.PKCS12KeyStore in the call stack. Hence, I think I need a P12 file (aka a PKCS#12 format file) rather than a .json file which means I need to go back to the client to ask for that - and from experience I think it may take some time to get the P12 file. I'll report back if/when i get anywhere.
UPDATE 2... figured it out, with Igor's help. I was wrongly specifying mapred.bq.auth.service.account.keyfile, it needed to be mapred.bq.auth.service.account.json.keyfile. Thus the pertinent section of code becomes:
conf = {
# Input Parameters.
'mapred.bq.project.id': project,
'mapred.bq.gcs.bucket': bucket,
'mapred.bq.temp.gcs.path': input_directory,
'mapred.bq.input.project.id': project_id,
'mapred.bq.input.dataset.id': dataset_id,
'mapred.bq.input.table.id': table_id,
'mapred.bq.auth.service.account.enable': 'true',
'mapred.bq.auth.service.account.json.keyfile': '/tmp/keyfile.json'
}
table_data = spark.sparkContext.newAPIHadoopRDD(
'com.google.cloud.hadoop.io.bigquery.JsonTextBigQueryInputFormat',
'org.apache.hadoop.io.LongWritable',
'com.google.gson.JsonObject',
conf=conf)
and the submit command is simply
gcloud dataproc jobs submit pyspark \
./bq_pyspark.py \
--cluster $CLUSTER \
--region $REGION
It now works, I am able to access data in biquery from spark-on-dataproc, authenticating using a service account json key file. Thank you Igor.
