Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-30436

CREATE EXTERNAL TABLE doesn't work without STORED AS

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 3.0.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      CREATE EXTERNAL TABLE doesn't work as before since Spark 3.0.0 preview 2. 

      SPARK-30098 describes the goal as `spark.sql.source.default` as default provider if there's no explicit "USING provider", but parser rule seems to be modified more than the goal which breaks CREATE EXTERNAL TABLE.

      scala> sql("CREATE EXTERNAL TABLE aaa (key INT, value STRING) USING HIVE LOCATION '/tmp/aaa'")
      org.apache.spark.sql.catalyst.parser.ParseException:
      Operation not allowed: CREATE EXTERNAL TABLE ...(line 1, pos 0)
      == SQL ==
      CREATE EXTERNAL TABLE aaa (key INT, value STRING) USING HIVE LOCATION '/tmp/aaa'
      ^^^  at org.apache.spark.sql.catalyst.parser.ParserUtils$.operationNotAllowed(ParserUtils.scala:41)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.$anonfun$visitCreateTable$1(AstBuilder.scala:2669)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.visitCreateTable(AstBuilder.scala:2666)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.super$visitCreateTable(SparkSqlParser.scala:188)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.$anonfun$visitCreateTable$1(SparkSqlParser.scala:188)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitCreateTable(SparkSqlParser.scala:184)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitCreateTable(SparkSqlParser.scala:55)
        at org.apache.spark.sql.catalyst.parser.SqlBaseParser$CreateTableContext.accept(SqlBaseParser.java:1537)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.$anonfun$visitSingleStatement$1(AstBuilder.scala:77)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.visitSingleStatement(AstBuilder.scala:77)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.$anonfun$parsePlan$1(ParseDriver.scala:77)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:113)
        at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:48)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:76)
        at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:605)
        at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111)
        at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:605)
        ... 47 elided

       

      scala> sql("CREATE EXTERNAL TABLE aaa (key INT, value STRING) LOCATION '/tmp/aaa'")
      org.apache.spark.sql.catalyst.parser.ParseException:
      Operation not allowed: CREATE EXTERNAL TABLE ...(line 1, pos 0)== SQL ==
      CREATE EXTERNAL TABLE aaa (key INT, value STRING) LOCATION '/tmp/aaa'
      ^^^  at org.apache.spark.sql.catalyst.parser.ParserUtils$.operationNotAllowed(ParserUtils.scala:41)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.$anonfun$visitCreateTable$1(AstBuilder.scala:2669)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.visitCreateTable(AstBuilder.scala:2666)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.super$visitCreateTable(SparkSqlParser.scala:188)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.$anonfun$visitCreateTable$1(SparkSqlParser.scala:188)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitCreateTable(SparkSqlParser.scala:184)
        at org.apache.spark.sql.execution.SparkSqlAstBuilder.visitCreateTable(SparkSqlParser.scala:55)
        at org.apache.spark.sql.catalyst.parser.SqlBaseParser$CreateTableContext.accept(SqlBaseParser.java:1537)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.$anonfun$visitSingleStatement$1(AstBuilder.scala:77)
        at org.apache.spark.sql.catalyst.parser.ParserUtils$.withOrigin(ParserUtils.scala:108)
        at org.apache.spark.sql.catalyst.parser.AstBuilder.visitSingleStatement(AstBuilder.scala:77)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.$anonfun$parsePlan$1(ParseDriver.scala:77)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:113)
        at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:48)
        at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:76)
        at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:605)
        at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111)
        at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:605)
        ... 47 elided
      

       

      It requires unique option for Hive table to let parser use the rule for Hive table. For example, STORED AS would work.

      scala> sql("CREATE EXTERNAL TABLE aaa (key INT, value STRING) STORED AS parquet LOCATION '/tmp/aaa'")
      20/01/06 21:08:36 WARN HiveConf: HiveConf of name hive.stats.jdbc.timeout does not exist
      20/01/06 21:08:36 WARN HiveConf: HiveConf of name hive.stats.retries.wait does not exist
      20/01/06 21:08:40 WARN ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 2.3.0
      20/01/06 21:08:40 WARN ObjectStore: setMetaStoreSchemaVersion called but recording version is disabled: version = 2.3.0, comment = Set by MetaStore jlim@192.168.1.216
      20/01/06 21:08:41 WARN SessionState: METASTORE_FILTER_HOOK will be ignored, since hive.security.authorization.manager is set to instance of HiveAuthorizerFactory.
      20/01/06 21:08:41 WARN HiveConf: HiveConf of name hive.internal.ss.authz.settings.applied.marker does not exist
      20/01/06 21:08:41 WARN HiveConf: HiveConf of name hive.stats.jdbc.timeout does not exist
      20/01/06 21:08:41 WARN HiveConf: HiveConf of name hive.stats.retries.wait does not exist
      res3: org.apache.spark.sql.DataFrame = []
      

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                kabhwan Jungtaek Lim
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: