@yangcm Below is the terminal output I used to test your create statements. You may need to do some additional hql for parquet formatting, but this should get you through to working with the tables:
[root@c7301 ~]# sudo su - hdfs
[hdfs@c7301 ~]$ hdfs dfs -mkdir -p /user/a/client
[hdfs@c7301 ~]$ hdfs dfs -mkdir -p /user/a/mysql
[hdfs@c7301 ~]$ hdfs dfs -chown -R hive:hive /user/a/
Then get to hive:
sudo su - hive
hive
And run your creation statements. I did them as single inserts in my test but you can do them together as an HQL file too. The key here is that the locations must be owned by hive or the user executing the hive command.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.0.0-78/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.0.0-78/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://c7301.ambari.apache.org:2181/default;password=hive;serviceDiscoveryMode=zooKeeper;user=hive;zooKeeperNamespace=hiveserver2
20/08/13 15:23:16 [main]: INFO jdbc.HiveConnection: Connected to c7301.ambari.apache.org:10000
Connected to: Apache Hive (version 3.1.0.3.1.0.0-78)
Driver: Hive JDBC (version 3.1.0.3.1.0.0-78)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.0.3.1.0.0-78 by Apache Hive
0: jdbc:hive2://c7301.ambari.apache.org:2181/> CREATE EXTERNAL TABLE default.a1 (
. . . . . . . . . . . . . . . . . . . . . . .> client_id smallint,
. . . . . . . . . . . . . . . . . . . . . . .> client_name varchar(255),
. . . . . . . . . . . . . . . . . . . . . . .> client_address varchar(1234)
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> PARTITIONED BY (month string)
. . . . . . . . . . . . . . . . . . . . . . .> stored as parquet
. . . . . . . . . . . . . . . . . . . . . . .> LOCATION '/user/a/client';
INFO : Compiling command(queryId=hive_20200813152342_b1597bb7-670b-4f18-9dbb-a9ee68cf1323): CREATE EXTERNAL TABLE default.a1 (
client_id smallint,
client_name varchar(255),
client_address varchar(1234)
)
PARTITIONED BY (month string)
stored as parquet
LOCATION '/user/a/client'
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20200813152342_b1597bb7-670b-4f18-9dbb-a9ee68cf1323); Time taken: 1.44 seconds
INFO : Executing command(queryId=hive_20200813152342_b1597bb7-670b-4f18-9dbb-a9ee68cf1323): CREATE EXTERNAL TABLE default.a1 (
client_id smallint,
client_name varchar(255),
client_address varchar(1234)
)
PARTITIONED BY (month string)
stored as parquet
LOCATION '/user/a/client'
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20200813152342_b1597bb7-670b-4f18-9dbb-a9ee68cf1323); Time taken: 0.584 seconds
INFO : OK
No rows affected (2.923 seconds)
0: jdbc:hive2://c7301.ambari.apache.org:2181/> CREATE EXTERNAL TABLE default.a2 (
. . . . . . . . . . . . . . . . . . . . . . .> mysql_12 double,
. . . . . . . . . . . . . . . . . . . . . . .> mysql_13 float
. . . . . . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . . . . . .> PARTITIONED BY (month string)
. . . . . . . . . . . . . . . . . . . . . . .> stored as parquet
. . . . . . . . . . . . . . . . . . . . . . .> LOCATION '/user/a/mysql';
INFO : Compiling command(queryId=hive_20200813152356_48370efa-c51c-466f-8b2d-cf8ebb30d55b): CREATE EXTERNAL TABLE default.a2 (
mysql_12 double,
mysql_13 float
)
PARTITIONED BY (month string)
stored as parquet
LOCATION '/user/a/mysql'
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hive_20200813152356_48370efa-c51c-466f-8b2d-cf8ebb30d55b); Time taken: 0.047 seconds
INFO : Executing command(queryId=hive_20200813152356_48370efa-c51c-466f-8b2d-cf8ebb30d55b): CREATE EXTERNAL TABLE default.a2 (
mysql_12 double,
mysql_13 float
)
PARTITIONED BY (month string)
stored as parquet
LOCATION '/user/a/mysql'
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20200813152356_48370efa-c51c-466f-8b2d-cf8ebb30d55b); Time taken: 0.084 seconds
INFO : OK
No rows affected (0.42 seconds)
0: jdbc:hive2://c7301.ambari.apache.org:2181/> describe default.a1
. . . . . . . . . . . . . . . . . . . . . . .> ;
INFO : Compiling command(queryId=hive_20200813152409_da398c2e-edc1-4109-bb07-5577133b02aa): describe default.a1
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:col_name, type:string, comment:from deserializer), FieldSchema(name:data_type, type:string, comment:from deserializer), FieldSchema(name:comment, type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling command(queryId=hive_20200813152409_da398c2e-edc1-4109-bb07-5577133b02aa); Time taken: 0.372 seconds
INFO : Executing command(queryId=hive_20200813152409_da398c2e-edc1-4109-bb07-5577133b02aa): describe default.a1
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20200813152409_da398c2e-edc1-4109-bb07-5577133b02aa); Time taken: 0.295 seconds
INFO : OK
+--------------------------+----------------+----------+
| col_name | data_type | comment |
+--------------------------+----------------+----------+
| client_id | smallint | |
| client_name | varchar(255) | |
| client_address | varchar(1234) | |
| month | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| month | string | |
+--------------------------+----------------+----------+
8 rows selected (0.891 seconds)
0: jdbc:hive2://c7301.ambari.apache.org:2181/> describe default.a2;
INFO : Compiling command(queryId=hive_20200813152426_a2916edf-e217-4b72-8398-6fd9d91dde60): describe default.a2
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:col_name, type:string, comment:from deserializer), FieldSchema(name:data_type, type:string, comment:from deserializer), FieldSchema(name:comment, type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling command(queryId=hive_20200813152426_a2916edf-e217-4b72-8398-6fd9d91dde60); Time taken: 0.293 seconds
INFO : Executing command(queryId=hive_20200813152426_a2916edf-e217-4b72-8398-6fd9d91dde60): describe default.a2
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20200813152426_a2916edf-e217-4b72-8398-6fd9d91dde60); Time taken: 0.179 seconds
INFO : OK
+--------------------------+------------+----------+
| col_name | data_type | comment |
+--------------------------+------------+----------+
| mysql_12 | double | |
| mysql_13 | float | |
| month | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| month | string | |
+--------------------------+------------+----------+
7 rows selected (0.778 seconds)
0: jdbc:hive2://c7301.ambari.apache.org:2181/>
If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.
Thanks,
Steven @ DFHZ
