cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Error writing data to Google Bigquery

Fernando_Messas
New Contributor II

Hello, I'm facing some problems while writing data to Google BigQuery. I'm able to read data from the same table, but when I try to append data I get the following error.

Error getting access token from metadata server at: http://169.254.169.254/computeMetadata/v1/instance/service-accounts/default/token

df.write.format("bigquery").mode("append") \
 
 .option("credentialsFile", "credentialfile.json") \
 
 .option("temporaryGcsBucket", "gs://bucketname/")\
 
 .option("parentProject", "projectId") \
 
 .option("project", "project") \
 
 .option("table","tablename") \
 
 .save()
java.io.IOException: Error getting access token from metadata server at: http://169.254.169.254/computeMetadata/v1/instance/service-accounts/default/token
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.util.CredentialFactory.getCredentialFromMetadataServiceAccount(CredentialFactory.java:259)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.util.CredentialFactory.getCredential(CredentialFactory.java:477)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystemBase.getCredential(GoogleHadoopFileSystemBase.java:1601)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystemBase.createGcsFs(GoogleHadoopFileSystemBase.java:1738)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystemBase.configure(GoogleHadoopFileSystemBase.java:1711)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystemBase.initialize(GoogleHadoopFileSystemBase.java:562)
	at shaded.databricks.V2_1_4.com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystemBase.initialize(GoogleHadoopFileSystemBase.java:515)
	at org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:2669)
	at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:370)
	at org.apache.hadoop.fs.Path.getFileSystem(Path.java:295)
	at com.google.cloud.spark.bigquery.BigQueryWriteHelper.<init>(BigQueryWriteHelper.scala:62)
	at com.google.cloud.spark.bigquery.BigQueryInsertableRelation.insert(BigQueryInsertableRelation.scala:41)
	at com.google.cloud.spark.bigquery.BigQueryRelationProvider.createRelation(BigQueryRelationProvider.scala:109)
	at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:48)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:73)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:71)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:94)
	at org.apache.spark.sql.execution.SparkPlan.$anonfun$execute$1(SparkPlan.scala:196)
	at org.apache.spark.sql.execution.SparkPlan.$anonfun$executeQuery$1(SparkPlan.scala:240)
	at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:165)
	at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:236)
	at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:192)
	at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:165)
	at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:164)
	at org.apache.spark.sql.DataFrameWriter.$anonfun$runCommand$1(DataFrameWriter.scala:1079)
	at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$5(SQLExecution.scala:126)
	at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:267)
	at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:104)
	at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:852)
	at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:77)
	at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:217)
	at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:1079)
	at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:468)
	at org.apache.spark.sql.DataFrameWriter.saveInternal(DataFrameWriter.scala:438)
	at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:311)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:380)
	at py4j.Gateway.invoke(Gateway.java:295)
	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
	at py4j.commands.CallCommand.execute(CallCommand.java:79)
	at py4j.GatewayConnection.run(GatewayConnection.java:251)
	at java.lang.Thread.run(Thread.java:748)
Caused by: shaded.databricks.V2_1_4.com.google.api.client.http.HttpResponseException: 404 Not Found
GET http://169.254.169.254/computeMetadata/v1/instance/service-accounts/default/token
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<title>404 - File or directory not found.</title>
<style type="text/css">
<!--
body{margin:0;font-size:.7em;font-family:Verdana, Arial, Helvetica, sans-serif;background:#EEEEEE;}
fieldset{padding:0 15px 10px 15px;} 
h1{font-size:2.4em;margin:0;color:#FFF;}
h2{font-size:1.7em;margin:0;color:#CC0000;} 
h3{font-size:1.2em;margin:10px 0 0 0;color:#000000;} 
#header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:"trebuchet MS", Verdana, sans-serif;color:#FFF;
background-color:#555555;}
#content{margin:0 0 0 2%;position:relative;}
.content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;}
-->
</style>
</head>
<body>
<div id="header"><h1>Server Error</h1></div>
<div id="content">
 <div class="content-container"><fieldset>
  <h2>404 - File or directory not found.</h2>
  <h3>The resource you are looking for might have been removed, had its name changed, or is temporarily unavailable.</h3>
 </fieldset></div>
</div>
</body>
</html>

1 ACCEPTED SOLUTION

Accepted Solutions

Prabakar
Databricks Employee
Databricks Employee

@Fernando Messas​ , if you are using the credential file in the notebook then you need to encode the base64 format.

You can try something like below:

val contentCred = "/dbfs/FileStore/keys/gcp/myCredentials.json"
val jsonBytes = Source.fromFile(contentCred).getLines.mkString
val myEvents = spark.read.format("bigquery")
   .option("credentials", new String(java.util.Base64.getEncoder.encode(jsonBytes.getBytes)))
   .option("parentProject", "myTestProj")
   .option("project", "project1") 
   .option("table","project1.my_data.events")
.load()
display(df)

View solution in original post

6 REPLIES 6

Fernando_Messas
New Contributor II

Thanks @Kaniz Fatma​ 

shan_chandra
Databricks Employee
Databricks Employee

could you please check the config value specified in spark.hadoop.fs.gs.auth.service.account.private.key ?

println(spark.conf.get("spark.hadoop.fs.gs.auth.service.account.private.key"))

Prabakar
Databricks Employee
Databricks Employee

@Fernando Messas​ , if you are using the credential file in the notebook then you need to encode the base64 format.

You can try something like below:

val contentCred = "/dbfs/FileStore/keys/gcp/myCredentials.json"
val jsonBytes = Source.fromFile(contentCred).getLines.mkString
val myEvents = spark.read.format("bigquery")
   .option("credentials", new String(java.util.Base64.getEncoder.encode(jsonBytes.getBytes)))
   .option("parentProject", "myTestProj")
   .option("project", "project1") 
   .option("table","project1.my_data.events")
.load()
display(df)

ambar2595
New Contributor II

@Prabakar Hello, Can you give me a sample specifically for writing in Pyspark? I'm also getting the same error.

jose_gonzalez
Databricks Employee
Databricks Employee

hi @Fernando Messas​ ,

Did Prabakar's or Shan's response fully answered your question, would you be happy to mark their answer as best so that others can quickly find the solution?

Please let us know If none of these questions works.

asif5494
New Contributor III

Sometime this error occur when your Private key or your service account key is not going in request header, So if you are using Spark or Databricks then you have to configure the JSON Key in Spark config so it will be added in request header.

Connect with Databricks Users in Your Area

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