LOAD DATA is not supported for datasource tables

user_14905864 0
Viewed 661

I am new to ADB and trying to load data into a table in databricks using a parquet file and i am giving the following command :

load data local inpath '/FileStore/tables/Subsidiary__1_-2.parquet' into table Subsidiary

But it is throwing the error as follows :

Error in SQL statement: AnalysisException: LOAD DATA is not supported for datasource tables: `default`.`subsidiary`;

Can anyone explain why this is happening

databricks apache-spark-sql apache-spark sql
2 Answers

According to the official documentation of Databricks about LOAD DATA (highlighting's mine):

Loads the data into a Hive SerDe table from the user specified directory or file.

According to the exception message (highlighting's mine) you use a Spark SQL table (datasource table):

AnalysisException: LOAD DATA is not supported for datasource tables: default.subsidiary;

The easiest is to DESCRIBE EXTENDED it and verify yourself that the Provider is not Hive but something else (e.g. parquet).

Demo

Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 3.0.1
      /_/

Using Scala version 2.12.10 (OpenJDK 64-Bit Server VM, Java 11.0.9)

scala> spark.range(5).write.saveAsTable("demo")

scala> sql("DESCRIBE EXTENDED demo").show(truncate = false)
20/12/29 21:57:35 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
+----------------------------+--------------------------------------------------------------+-------+
|col_name                    |data_type                                                     |comment|
+----------------------------+--------------------------------------------------------------+-------+
|id                          |bigint                                                        |null   |
|                            |                                                              |       |
|# Detailed Table Information|                                                              |       |
|Database                    |default                                                       |       |
|Table                       |demo                                                          |       |
|Owner                       |jacek                                                         |       |
|Created Time                |Tue Dec 29 21:57:09 CET 2020                                  |       |
|Last Access                 |UNKNOWN                                                       |       |
|Created By                  |Spark 3.0.1                                                   |       |
|Type                        |MANAGED                                                       |       |
|Provider                    |parquet                                                       |       |
|Statistics                  |2582 bytes                                                    |       |
|Location                    |file:/Users/jacek/dev/oss/spark/spark-warehouse/demo          |       |
|Serde Library               |org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe   |       |
|InputFormat                 |org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat |       |
|OutputFormat                |org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat|       |
+----------------------------+--------------------------------------------------------------+-------+

scala> sql("load data local inpath 'NOTICE' into table demo")
org.apache.spark.sql.AnalysisException: LOAD DATA is not supported for datasource tables: `default`.`demo`;
  at org.apache.spark.sql.execution.command.LoadDataCommand.run(tables.scala:317)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.executeCollect(commands.scala:79)
  at org.apache.spark.sql.Dataset.$anonfun$logicalPlan$1(Dataset.scala:229)
  at org.apache.spark.sql.Dataset.$anonfun$withAction$1(Dataset.scala:3618)
  at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withNewExecutionId$5(SQLExecution.scala:100)
  at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:160)
  at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withNewExecutionId$1(SQLExecution.scala:87)
  at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:764)
  at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:64)
  at org.apache.spark.sql.Dataset.withAction(Dataset.scala:3616)
  at org.apache.spark.sql.Dataset.<init>(Dataset.scala:229)
  at org.apache.spark.sql.Dataset$.$anonfun$ofRows$2(Dataset.scala:100)
  at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:764)
  at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:97)
  at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:607)
  at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:764)
  at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:602)
  ... 47 elided
user_13053440

Update: see the answer by Jacek, but it works only in specific situations

There is no such command in Spark SQL as load data. The closest one is INSERT INTO that allows to insert data into table from other table.

But if you really want to access data in the given file, then you can use CREATE TABLE instead. Something like this:

CREATE TABLE IF NOT EXISTS Subsidiary
USING PARQUET
LOCATION '/FileStore/tables/Subsidiary__1_-2.parquet'
Alex_Ott_186270
Related