問題一：直接在指令行建立的parquet格式的表通過spark saveAsTable 無法寫入
1.建表語句
CREATE TABLE parquet_test (
name string,
sex string,
age int
)
STORED AS PARQUET;
2.檢視表結構
3.通過代碼直接save
//save 主要代碼
sparksession.createDataFrame(rdd1).write.mode("append").saveAsTable("parquet_test")
//因為spark預設格式為parquet，是以format("parquet")寫于不寫影響不大
//sparksession.createDataFrame(rdd1).write.format("parquet").mode("append").saveAsTable("parquet_test")
直接save發現會報錯，然後将寫入的表名字換掉讓spark自動去建表，然後去檢視和上邊的表有什麼不同
4.檢視spark自動建表的表結構
5.根據不同的報錯資訊對表結構進行修改
//報錯資訊
Exception in thread "main" org.apache.spark.sql.AnalysisException: The format of the existing table db_src.parquet_test is `HiveFileFormat`. It doesn't match the specified format `ParquetFileFormat`.;
//解決辦法
ALTER TABLE parquet_test SET TBLPROPERTIES ('spark.sql.sources.provider'='parquet');
//報錯資訊
Exception in thread "main" org.apache.spark.sql.AnalysisException: The column number of the existing table db_src.parquet_test(struct<>) doesn't match the data schema(struct<name:string,sex:string,age:int>);
//解決辦法
ALTER TABLE parquet_test SET TBLPROPERTIES ('spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sex\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"age\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}}]}');
//報錯資訊
Exception in thread "main" org.apache.spark.sql.AnalysisException: Could not read schema from the hive metastore because it is corrupted.;
//解決辦法
ALTER TABLE parquet_test SET TBLPROPERTIES ('spark.sql.sources.schema.numParts'='1');
//報錯資訊
Exception in thread "main" java.lang.IllegalArgumentException: Expected exactly one path to be specified, but got:
//解決辦法
ALTER TABLE parquet_test SET SERDEPROPERTIES ('path'='hdfs://nameservice1/user/hive/warehouse/db_src.db/parquet_test');
将上述操作做完之後就能正常寫入，需要注意的是不同的環境可能修改的參數不同，我是在生産和測試兩個環境修改的參數都不一樣，主要思路就是根據報錯資訊，選擇性的進行參數修改。另外也可以在建表的時候直接指定參數，但是我這裡沒有成功，下面我會貼出來hive官方文檔的建表語句，如果後面我試驗成功了，我會繼續分享出來
6.官方文檔，詳細的建表語句
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
column_constraint_specification:
: [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
default_value:
: [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]
constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
[, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
問題二：對已經存在的表進行加字段
//添加字段class
alter table test_mm add columns(class string);
//在schema增加class的資訊
ALTER TABLE test_mm SET TBLPROPERTIES ('spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sex\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"age\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"class \",\"type\":\"string\",\"nullable\":true,\"metadata\":{}}]}');
如有不對的地方，歡迎大家指正