Databricks中Spark SQL创建External Table的方法与语法咨询

两类表核心区别 #

• Managed table（托管表）：Spark 同时管理表的元数据（metadata）与实际数据，Databricks 环境下元数据和数据默认都存储在账户对应的 DBFS 路径下。执行DROP TABLE操作时，会同步删除元数据和对应存储路径下的所有实际数据。
• External table（外部表/非托管表）：Spark 仅管理表的元数据，实际数据的存储路径完全由用户自定义控制。执行DROP TABLE操作时，只会删除元数据，用户指定路径下的实际数据会完整保留，不会被清理。

三种创建语法的理解校验 #

查询1：LOCATION指定路径映射存量数据 #

CREATE TABLE test_tbl 
USING CSV 
LOCATION '/mnt/csv_files'

这个理解部分正确：

• 当指定的/mnt/csv_files路径下已经存在CSV格式的存量数据时，执行这条语句不会额外创建新目录、也不会生成新的数据文件，表直接映射该路径下的已有数据。
• 关于数据变更同步的问题：对该表执行INSERT、UPDATE、DELETE操作时，所有变更都会直接同步到/mnt/csv_files路径下的对应数据文件。外部表仅在数据生命周期管控上和托管表有区别，读写逻辑完全一致，所有操作都会直接作用在用户指定的存储路径上。
• 补充：如果指定路径不存在，执行建表语句时也会自动创建对应目录，后续写入的数据会直接存到该路径下。

查询2：OPTIONS内PATH参数指定路径 #

CREATE TABLE test_tbl(id STRING, value STRING) 
USING PARQUET 
OPTIONS (PATH '/mnt/test_tbl')

这个理解存在偏差：

• 首先明确核心结论：在Spark 3.x及以上版本、Databricks Runtime所有正式版本中，OPTIONS (PATH 'xxx')和LOCATION 'xxx'没有功能差异，二者都是用来指定外部表的自定义存储路径，最终效果完全一致：执行建表时如果路径不存在会自动创建目录，后续写入的数据都会存在该路径下，删表时不会清理路径下的数据。
• 二者仅在语法层面有区别：LOCATION是建表语句的标准顶层子句，属于ANSI SQL标准语法的一部分；PATH是数据源的通用配置项，写在OPTIONS里本质是给Parquet等数据源传入存储路径参数，Spark 2.x及更早版本存在仅识别OPTIONS内PATH参数的情况，目前两个写法已经完全对齐。
• 注意：建表时不允许同时配置LOCATION和OPTIONS内的PATH为不同值，否则会直接抛出语法冲突错误。

查询3：CTAS语法带LOCATION建表 #

CREATE TABLE test_tbl 
LOCATION '/mnt/test_tbl'
AS SELECT * FROM tmp

这个理解完全准确：

• 这条语句属于CTAS（CREATE TABLE AS SELECT）语法创建外部表，执行时会先创建/mnt/test_tbl对应目录，然后将tmp视图/表的查询结果以表配置的格式（Spark默认Parquet，Databricks默认Delta）写入该路径，同时注册表的元数据。
• 后续对test_tbl的所有增删改查操作，都只会作用在/mnt/test_tbl路径下生成的数据文件，不会对来源视图tmp以及tmp依赖的源数据产生任何影响。
• 补充：CTAS语法创建外部表时，LOCATION指定的路径必须是空目录或者不存在的目录，否则会抛出路径非空的报错，避免误覆盖存量数据。

其他创建外部表的常用方法 #

除了上述三种SQL写法，还有三类高频使用的外部表创建方式：

• 方式1：显式使用EXTERNAL关键字创建
  这是语义最清晰的写法，完全兼容标准Hive SQL语法，所有支持Hive兼容模式的Spark版本都可以使用，效果和不带EXTERNAL、仅指定LOCATION的建表语句完全一致：

CREATE EXTERNAL TABLE test_tbl(id STRING, value STRING)
USING DELTA
LOCATION '/mnt/delta_tbl';

• 方式2：通过DataFrame API编程创建
  不管使用Scala/Java/Python/R哪种Spark API，调用saveAsTable方法时手动指定path参数，Spark就会自动创建外部表，不会把数据存在默认的托管表仓库路径，删表时也不会删除指定路径下的数据，以PySpark为例：

# 读取源数据得到DataFrame
df = spark.read.csv("/mnt/source_csv", header=True)
# 指定自定义存储路径写入，自动注册为外部表
df.write.format("parquet").option("path", "/mnt/ext_tbl_path").saveAsTable("test_tbl")

• 方式3：分区外部表批量加载存量分区
  针对已经按分区规则存储好的存量数据，可以先定义表结构和分区规则，再通过MSCK REPAIR TABLE自动扫描路径下所有符合命名规则的分区，不需要手动逐个添加分区：

CREATE EXTERNAL TABLE test_tbl(id STRING, value STRING)
USING PARQUET
PARTITIONED BY (dt STRING)
LOCATION '/mnt/partitioned_parquet';
-- 自动扫描路径下所有dt=xxx格式的分区目录，加载分区元数据
MSCK REPAIR TABLE test_tbl;

内容的提问来源于stack exchange，提问作者Minura Punchihewa