建表语句,内部表，外部表

已于 2022-04-09 14:49:59 修改
阅读量1k 收藏
点赞数
分类专栏： hive基础 初学大数据 文章标签： hive
于 2022-04-06 19:23:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ZZJXP/article/details/123984983
版权

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  // 定义字段名，字段类型
  [(col_name data_type [COMMENT col_comment], ...)]
  // 给表加上注解
  [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]
  [
      // 指定设置行、列分隔符 
   [ROW FORMAT row_format] 
   // 指定Hive储存格式：textFile、rcFile、SequenceFile 默认为：textFile
   [STORED AS file_format]
   
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定储存位置
  [LOCATION hdfs_path]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL对hbase数据进行查询，当然速度比较慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符 

hive> create database test2;
OK
Time taken: 0.528 seconds
hive> use test2;
OK
Time taken: 0.012 seconds
hive> create table students
    > (
    >     id bigint,
    >     name string,
    >     age int,
    >     gender string,
    >     clazz string
    > );
OK
Time taken: 0.157 seconds
hive> show create table students;
OK
CREATE TABLE `students`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/test2.db/students'
TBLPROPERTIES (
  'transient_lastDdlTime'='1649226473')
Time taken: 0.027 seconds, Fetched: 16 row(s)
hive> show create table test1.students;
OK
CREATE TABLE `test1.students`(
  `id` bigint COMMENT 'fid', 
  `name` string COMMENT 'f�
', 
  `age` int COMMENT 'ft�', 
  `gender` string COMMENT 'f'+', 
  `clazz` string COMMENT 'f�')
COMMENT 'f�oh'
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/test1.db/students'
TBLPROPERTIES (
  'transient_lastDdlTime'='1648889522')
Time taken: 0.047 seconds, Fetched: 18 row(s)

 create table students2_1
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stu/input'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location

hive> create table students2_1
    > (
    >     id bigint,
    >     name string,
    >     age int,
    >     gender string,
    >     clazz string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    > LOCATION '/data/stu/input'; 
OK
Time taken: 0.046 seconds
hive> show create table students2_1;
OK
CREATE TABLE `students2_1`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/data/stu/input'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'numFiles'='0', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1649226836')
Time taken: 0.027 seconds, Fetched: 21 row(s)
hive> select * from students2_1 limit 10;
OK
1500100001	施笑槐	22	女	文科六班
1500100002	吕金鹏	24	男	文科六班
1500100003	单乐蕊	22	女	理科六班
1500100004	葛德曜	24	男	理科三班
1500100005	宣谷芹	22	女	理科五班
1500100006	边昂雄	21	男	理科二班
1500100007	尚孤风	23	女	文科六班
1500100008	符半双	22	女	理科六班
1500100009	沈德昌	21	男	理科一班
1500100010	羿彦昌	23	男	理科六班
Time taken: 0.042 seconds, Fetched: 10 row(s)

create external table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stu/input'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location

hive> 
    > create external table students2
    > (
    >     id bigint,
    >     name string,
    >     age int,
    >     gender string,
    >     clazz string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    > LOCATION '/data/stu/input'; 
OK
Time taken: 0.034 seconds
hive> select *from students2 limit 10
    > ;
OK
1500100001	施笑槐	22	女	文科六班
1500100002	吕金鹏	24	男	文科六班
1500100003	单乐蕊	22	女	理科六班
1500100004	葛德曜	24	男	理科三班
1500100005	宣谷芹	22	女	理科五班
1500100006	边昂雄	21	男	理科二班
1500100007	尚孤风	23	女	文科六班
1500100008	符半双	22	女	理科六班
1500100009	沈德昌	21	男	理科一班
1500100010	羿彦昌	23	男	理科六班
Time taken: 0.13 seconds, Fetched: 10 row(s)
hive> show create table students2;
OK
CREATE EXTERNAL TABLE `students2`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/data/stu/input'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'numFiles'='0', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1649226023')
Time taken: 0.034 seconds, Fetched: 21 row(s)
[root@master ~]# cd /usr/local/soft
[root@master soft]# cd data/
[root@master data]# ls
new_db.sql  student.sql         theZen.txt
score.sql   students.txt        wordcount
score.txt   theZenOfPython.txt  words.txt
[root@master data]# hdfs dfs -put students.txt /user/hive/warehouse/test2.db/students
hive> select * from students limit 10;
OK
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
Time taken: 0.041 seconds, Fetched: 10 row(s)
hive> show create table students;
OK
CREATE TABLE `students`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/test2.db/students'
TBLPROPERTIES (
  'transient_lastDdlTime'='1649226473')
Time taken: 0.024 seconds, Fetched: 16 row(s)
hive> drop table students;
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students' to trash at: hdfs://master:9000/user/root/.Trash/Current
OK
Time taken: 0.197 seconds
hive> show create table students2;
OK
CREATE EXTERNAL TABLE `students2`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/data/stu/input'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'numFiles'='0', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1649230136')
Time taken: 0.021 seconds, Fetched: 21 row(s)
hive> drop table students2;
OK
Time taken: 0.052 seconds

// 内部表
create table students_internal
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';

// 外部表
create external table students_external
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';

 create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

hive> create table students_orc
    > (
    >     id bigint,
    >     name string,
    >     age int,
    >     gender string,
    >     clazz string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    > STORED AS orcfile;
OK
Time taken: 0.057 seconds
hive> create table students_parquet
    > (
    >     id bigint,
    >     name string,
    >     age int,
    >     gender string,
    >     clazz string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    > STORED AS parquetfile;
OK
Time taken: 0.044 seconds
hive> show tables;
OK
students2_1
students_orc
students_parquet
Time taken: 0.012 seconds, Fetched: 3 row(s)
hive> show create table students_orc
    > ;
OK
CREATE TABLE `students_orc`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.ORCFileInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.ORCFileOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/test2.db/students_orc'
TBLPROPERTIES (
  'transient_lastDdlTime'='1649230990')
Time taken: 0.023 seconds, Fetched: 16 row(s)
hive> show create table students_parquet
    > ;
OK
CREATE TABLE `students_parquet`(
  `id` bigint, 
  `name` string, 
  `age` int, 
  `gender` string, 
  `clazz` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/test2.db/students_parquet'
TBLPROPERTIES (
  'transient_lastDdlTime'='1649231044')
Time taken: 0.02 seconds, Fetched: 16 row(s)


[root@master data]# hdfs dfs -put students.txt /user/hive/warehouse/test2.db/students_orc
[root@master data]# hdfs dfs -put students.txt /user/hive/warehouse/test2.db/students_parquet
hive> select * from students_orc limit 10;
OK
Failed with exception java.io.IOException:java.io.IOException: hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/students.txt not a RCFile and has magic of 150
Time taken: 0.029 seconds
hive> select * from students_parquet  limit 10;
OK
Failed with exception java.io.IOException:java.lang.RuntimeException: hdfs://master:9000/user/hive/warehouse/test2.db/students_parquet/students.txt is not a Parquet file. expected magic number at tail [80, 65, 82, 49] but found [-83, -25, -113, -83]
Time taken: 0.031 seconds
[root@master data]# hdfs dfs -rmr /user/hive/warehouse/test2.db/students_orc/students.txt
rmr: DEPRECATED: Please use 'rm -r' instead.
22/04/06 16:09:27 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 1440 minutes, Emptier interval = 0 minutes.
22/04/06 16:09:27 INFO fs.TrashPolicyDefault: Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/students.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current/user/hive/warehouse/test2.db/students_orc/students.txt1649232567736
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/students.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current
hive> select * from students2_1 limit 2;
OK
1500100001	施笑槐	22	女	文科六班
1500100002	吕金鹏	24	男	文科六班
Time taken: 0.032 seconds, Fetched: 2 row(s)
hive> insert into table students_orc select * from students2_1;
Query ID = root_20220406161318_0b1866bf-21e4-4f93-ab93-18c8e24998c0
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649211406017_0001, Tracking URL = http://master:8088/proxy/application_1649211406017_0001/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-06 16:13:29,142 Stage-1 map = 0%,  reduce = 0%
2022-04-06 16:13:35,486 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.4 sec
MapReduce Total cumulative CPU time: 1 seconds 400 msec
Ended Job = job_1649211406017_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/.hive-staging_hive_2022-04-06_16-13-18_263_1846120048648544602-1/-ext-10000
Loading data to table test2.students_orc
Table test2.students_orc stats: [numFiles=1, numRows=1000, totalSize=7253, rawDataSize=288000]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.4 sec   HDFS Read: 45760 HDFS Write: 7333 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 400 msec
OK
Time taken: 19.435 seconds
hive> select * from students_orc limit 10;
OK
1500100001	施笑槐	22	女	文科六班
1500100002	吕金鹏	24	男	文科六班
1500100003	单乐蕊	22	女	理科六班
1500100004	葛德曜	24	男	理科三班
1500100005	宣谷芹	22	女	理科五班
1500100006	边昂雄	21	男	理科二班
1500100007	尚孤风	23	女	文科六班
1500100008	符半双	22	女	理科六班
1500100009	沈德昌	21	男	理科一班
1500100010	羿彦昌	23	男	理科六班
Time taken: 0.033 seconds, Fetched: 10 row(s)
hive> select count(*) from students_orc;
Query ID = root_20220406161652_04fc529a-1e88-4dff-8383-ca6f3a83dbf5
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1649211406017_0002, Tracking URL = http://master:8088/proxy/application_1649211406017_0002/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2022-04-06 16:17:02,774 Stage-1 map = 0%,  reduce = 0%
2022-04-06 16:17:07,908 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.66 sec
2022-04-06 16:17:14,039 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.62 sec
MapReduce Total cumulative CPU time: 1 seconds 620 msec
Ended Job = job_1649211406017_0002
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 1.62 sec   HDFS Read: 14219 HDFS Write: 5 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 620 msec
OK
1000
Time taken: 22.629 seconds, Fetched: 1 row(s)

[root@master data]# hdfs dfs -rmr /user/hive/warehouse/test2.db/students_parquet/students.txt
rmr: DEPRECATED: Please use 'rm -r' instead.
22/04/06 16:21:52 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 1440 minutes, Emptier interval = 0 minutes.
22/04/06 16:21:52 INFO fs.TrashPolicyDefault: Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students_parquet/students.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current/user/hive/warehouse/test2.db/students_parquet/students.txt
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students_parquet/students.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current
hive> insert into table students_parquet select * from students2_1;
Query ID = root_20220406161924_eee5417d-0312-4656-b58c-3e530ae3dec6
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649211406017_0003, Tracking URL = http://master:8088/proxy/application_1649211406017_0003/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-06 16:19:33,118 Stage-1 map = 0%,  reduce = 0%
2022-04-06 16:19:39,247 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.24 sec
MapReduce Total cumulative CPU time: 1 seconds 240 msec
Ended Job = job_1649211406017_0003
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://master:9000/user/hive/warehouse/test2.db/students_parquet/.hive-staging_hive_2022-04-06_16-19-24_869_8919741910758401213-1/-ext-10000
Loading data to table test2.students_parquet
Table test2.students_parquet stats: [numFiles=2, numRows=1000, totalSize=64819, rawDataSize=5000]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.24 sec   HDFS Read: 46015 HDFS Write: 22903 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 240 msec
OK
Time taken: 15.547 seconds
hive> create table students5 as select * from students2_1;
Query ID = root_20220406162622_37d32527-9077-42c6-850b-c368fcfe3dd1
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649211406017_0004, Tracking URL = http://master:8088/proxy/application_1649211406017_0004/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-06 16:26:29,990 Stage-1 map = 0%,  reduce = 0%
2022-04-06 16:26:35,112 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.78 sec
MapReduce Total cumulative CPU time: 780 msec
Ended Job = job_1649211406017_0004
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://master:9000/user/hive/warehouse/test2.db/.hive-staging_hive_2022-04-06_16-26-22_678_6947166971297328109-1/-ext-10001
Moving data to: hdfs://master:9000/user/hive/warehouse/test2.db/students5
Table test2.students5 stats: [numFiles=1, numRows=1000, totalSize=41000, rawDataSize=40000]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 0.78 sec   HDFS Read: 45272 HDFS Write: 41076 SUCCESS
Total MapReduce CPU Time Spent: 780 msec
OK
Time taken: 13.542 seconds
hive> desc students5;
OK
id                  	bigint              	                    
name                	string              	                    
age                 	int                 	                    
gender              	string              	                    
clazz               	string              	                    
Time taken: 0.04 seconds, Fetched: 5 row(s)
hive> create table students4 like students2_1;
OK
Time taken: 0.043 seconds
hive> 
    > select * from students4;
OK
Time taken: 0.052 seconds
hive> create table students6 as select id,name from student2_1;
hive> truncate table students6;
OK
Time taken: 0.048 seconds
hive> insert overwrite table students_orc select * from students2_1;
Query ID = root_20220406164128_ce9bfa5d-6c7f-4cb1-a569-b2358fbe229a
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649211406017_0006, Tracking URL = http://master:8088/proxy/application_1649211406017_0006/
Kill Command = /usr/local/soft/hadoop-2.7.6/bin/hadoop job  -kill job_1649211406017_0006
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2022-04-06 16:41:36,675 Stage-1 map = 0%,  reduce = 0%
2022-04-06 16:41:41,835 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.27 sec
MapReduce Total cumulative CPU time: 1 seconds 270 msec
Ended Job = job_1649211406017_0006
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/.hive-staging_hive_2022-04-06_16-41-28_893_8152676924006567408-1/-ext-10000
Loading data to table test2.students_orc
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students_orc/000000_0' to trash at: hdfs://master:9000/user/root/.Trash/Current
Table test2.students_orc stats: [numFiles=1, numRows=1000, totalSize=7253, rawDataSize=288000]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.27 sec   HDFS Read: 46029 HDFS Write: 7333 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 270 msec
OK
Time taken: 15.11 seconds
hive> alter table students2_1 rename to students2;
OK
Time taken: 0.054 seconds
hive> show tables;
OK
students2
students4
students5
students6
students_orc
students_parquet
Time taken: 0.009 seconds, Fetched: 6 row(s)

hive> dfs -ls /
    > ;
Found 4 items
drwxr-xr-x   - lenovo supergroup          0 2022-03-23 10:04 /TestAPI
drwxr-xr-x   - root   supergroup          0 2022-03-27 20:13 /data
drwx------   - root   supergroup          0 2022-04-02 16:34 /tmp
drwx------   - root   supergroup          0 2022-04-02 16:36 /user
hive> dfs -df -h /
    > ;
Filesystem            Size   Used  Available  Use%
hdfs://master:9000  93.9 G  9.4 M     82.9 G    0%

 // 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 移动、移动、移动
load data inpath '/input1/students.txt' into table students;

// 清空表
truncate table students;
// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
load data local inpath '/usr/local/soft/data/students.txt' into table students;
// overwrite 覆盖加载
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

[root@master data]# pwd
/usr/local/soft/data
hive> load data local inpath '/usr/local/soft/data/students.txt' into table students4;
Loading data to table test2.students4
Table test2.students4 stats: [numFiles=1, totalSize=41998]
OK
Time taken: 0.504 seconds
hive> load data inpath '/data/stu/input/students.txt' into table students4;
Loading data to table test2.students4
Table test2.students4 stats: [numFiles=2, totalSize=83996]
OK
Time taken: 0.158 seconds
hive>  load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students4;
Loading data to table test2.students4
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students4/students.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current
Moved: 'hdfs://master:9000/user/hive/warehouse/test2.db/students4/students_copy_1.txt' to trash at: hdfs://master:9000/user/root/.Trash/Current
Table test2.students4 stats: [numFiles=1, numRows=0, totalSize=41998, rawDataSize=0]
OK
Time taken: 0.595 seconds
hive> show databases;
OK
default
test1
test2
Time taken: 0.01 seconds, Fetched: 3 row(s)
hive> drop database test1;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database test1 is not empty. One or more tables exist.)
hive> drop database test1 cascade;
Moved: 'hdfs://master:9000/user/hive/warehouse/test1.db/score' to trash at: hdfs://master:9000/user/root/.Trash/Current
Moved: 'hdfs://master:9000/user/hive/warehouse/test1.db/students' to trash at: hdfs://master:9000/user/root/.Trash/Current
Moved: 'hdfs://master:9000/user/hive/warehouse/test1.db' to trash at: hdfs://master:9000/user/root/.Trash/Current
OK
Time taken: 0.311 seconds
hive> show databases;
OK
default
test2
Time taken: 0.009 seconds, Fetched: 2 row(s)
hive> desc students2;
OK
id                  	bigint              	                    
name                	string              	                    
age                 	int                 	                    
gender              	string              	                    
clazz               	string              	                    
Time taken: 0.062 seconds, Fetched: 5 row(s)
hive> desc extended students2;
OK
id                  	bigint              	                    
name                	string              	                    
age                 	int                 	                    
gender              	string              	                    
clazz               	string              	                    
	 	 
Detailed Table Information	Table(tableName:students2, dbName:test2, owner:root, createTime:1649226836, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:bigint, comment:null), FieldSchema(name:name, type:string, comment:null), FieldSchema(name:age, type:int, comment:null), FieldSchema(name:gender, type:string, comment:null), FieldSchema(name:clazz, type:string, comment:null)], location:hdfs://master:9000/user/hive/warehouse/test2.db/students2, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{field.delim=,, serialization.format=,}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{last_modified_time=1649234943, totalSize=0, numRows=-1, rawDataSize=-1, COLUMN_STATS_ACCURATE=false, numFiles=0, transient_lastDdlTime=1649234943, last_modified_by=root}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)	
Time taken: 0.045 seconds, Fetched: 7 row(s)
hive> desc formatted students2;
OK
# col_name            	data_type           	comment             
	 	 
id                  	bigint              	                    
name                	string              	                    
age                 	int                 	                    
gender              	string              	                    
clazz               	string              	                    
	 	 
# Detailed Table Information	 	 
Database:           	test2               	 
Owner:              	root                	 
CreateTime:         	Wed Apr 06 14:33:56 CST 2022	 
LastAccessTime:     	UNKNOWN             	 
Protect Mode:       	None                	 
Retention:          	0                   	 
Location:           	hdfs://master:9000/user/hive/warehouse/test2.db/students2	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 
	COLUMN_STATS_ACCURATE	false               
	last_modified_by    	root                
	last_modified_time  	1649234943          
	numFiles            	0                   
	numRows             	-1                  
	rawDataSize         	-1                  
	totalSize           	0                   
	transient_lastDdlTime	1649234943          
	 	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputForma 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 
	field.delim         	,                   
	serialization.format	,                   
Time taken: 0.044 seconds, Fetched: 38 row(s)

小胖超凶哦！
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
大数据学习之路26-hive的基本建表语句，内外部表的区别，删除表导入数据
爱米酱的博客
08-19 506
1.建库 hive中有一个默认的库： 库名：default 库目录：hdfs://marshal:9000/user/hive/warehouse 新建库： create database db_order; 我们的数据库在DBS中可以看到： 库建好后，在hdfs中会生成一个库目录，目录名为库名.db 2.基本建表语句 use db_order; create tab...
oracle外部表建表语句,Oracle 外部表创建（转）
weixin_42502811的博客
04-04 331
Oracle 外部表创建实验Oracle为外部表提供了两种访问的驱动程序，默认的访问程序是oracle_loader,它允许使用oracle的装载技术从外部文件中读取数据。另一种访问驱动程序是oracle_datapump它从数据库读取数据库并将数据插入到外部表中。1． Oracle_loader驱动程序创建外部表去访问D:\waibubiao 下exp.dat文件中的记录(1).创建一个目录c...
oracle 创建外部表实例及常用语句解释
inabaraku的博客
06-09 615
create table test2  (      AC_ID VARCHAR2(50),--描述列        AC_SEQN VARCHAR2(50),        NAME VARCHAR2(50),        CUR_NO VARCHAR2(50),        PRDT_NO VARCHAR2(50),        ACC_NO VARCHAR2(50),
Hive建表语句详解--CREATE TABLE
热门推荐
Thomson617的博客
01-09 14万+
Hive创建表的方式（默认路径/user/hive/warehouse，也可以location指定，主要针对external表）1、使用create命令创建一个新表,带分区可以使用describe formatted mytest_deptaddr 查看建表相关的各种配置属性以及默认属性。从下面可以看出，在创建表时有很多属性，比如存储地址，存储格式等属性我们都没有直接配置，而是选择了系统默认的。2、把一张表的某些字段抽取出来，创建成一张新表，使用as注意： 1.as只会复制属性以及属性值到新的表中。
Hive DDL建表语句与内部表、外部表、分区表、分桶表
qq_59472803的博客
10-03 1669
1、SQl 四大定义语言 DDL： 数据库定义语言 注意: 不涉及对表数据的操作 DML： 数据库操作语言 DQL ：数据库查询语言 DCL：数据库控制语言 Hive的建库建表语句 tempproary：临时，通过此关键词可以创建一张临时表（仅在当前会话有效） eg： create tempproary table if not exists 数据库名.表名； external：外部的 通过此关键词可以创建一个外部表 eg： create external table if not exists 数
java实体转mysql建表语句
12-29
好用的java实体转MySQL建表语句，非常实用，不好使你找我！
声明分区表数据以及建表语句
06-17
声明式分区表 数据
根据Excel生成建表语句工具.zip
12-13
根据excel模板生成建表语句
quartz-数据库建表语句.zip
09-26
Quartz 数据库建表语句 大全！
根据Excel生成建表语句源码.rar
12-13
根据Excel生成建表语句sql源码
Hive内部表和外部表
主要分享大数据相关的知识，如Spark、Hudi
12-05 1896
我的原创地址：https://dongkelun.com/2018/12/03/hiveInternalAndExternalTables/ 前言 总结一下Hive的内部表和外部表以及两者的区别。 1、建表语句 1.1 内部表 平时创建的普通表为内部表 create table `test_internal` ( id string comment 'ID', name string comme...
hive外部表改为内部表_Hive内部表和外部表
weixin_39809584的博客
12-22 830
前言总结一下Hive的内部表和外部表以及两者的区别。1、建表语句1.1 内部表平时创建的普通表为内部表createtable`test_internal`(idstringcomment'ID',namestringcomment'名字')comment'测试内部表'ROWFORMATDELIMITEDFIELDSTERMINATEDBY',';外部表带exter...
大数据时代--Hive数据定义
王洋的专栏
09-14 3818
Hive中数据库的相关操作 数据库的创建、删除 hive (default)> create database if not exists wyTest; OK Time taken: 0.69 seconds hive (default)> show databases; OK default wytest Time taken: 0.031 seconds, Fetched: 2 row(
HIVE Statistics(Hive 统计信息) 的说明和查询优化详解
houzhizhen的专栏
03-09 8131
本文从普通用户的角度讲述 Hive 统计信息，源代码角度请参考。 统计信息的分类 有两种统计信息，第1种为表和分区的统计信息，第2种为分区的统计信息。 表和分区的统计信息 表的统计信息 包括如下内容： numFiles – 文件的数量 numRows – 记录数量 totalSize – 总文件大小 rawDataSize – 原始数据量（因为可以压缩后存储到文件里） 对于分区表，还包括以下内容： numPartitions 1824 显示表的统计信息的语句。 des
Hive基础（建表（内部表，外部表）&插入数据&导出命令&数据查询）
ShiningBlueOcean
02-27 3218
Hive hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行 Hive操作和操作各种关系数据库基本语法很相似，但是也有特别之处，本篇主要讲述Hive操作和你所熟悉的sql语句的...
Hive表的修改Alter
u011495642的博客
11-07 1097
原文地址：https://www.cnblogs.com/skyl/p/4736587.html     Hive表的修改Alter 1.查看创建表的信息 【show create table】 hive&gt; show create table student; OK createtab_stmt CREATE TABLE `student`( `age` int, ...
hive-redis运行Log日志
知识的力量
04-23 2128
[root@hadoop1 testData]# sh hive-redis.sh  >  /opt/testData/hive-redis.log   2>&1  & rmr: DEPRECATED: Please use 'rm -r' instead. 17/04/23 10:55:07 INFO fs.TrashPolicyDefault: Namenode trash configu
hive 中显示创建的hql语句及 desc 命令实战
qq_20754935的博客
11-19 1万+
1、显示创建的hql语句 show tables; show create table student_info; hive (default)> show create table student_info; OK createtab_stmt CREATE TABLE `student_info`(   `s_id` int,    `course` string,
Hive入门--6.表的基本操作
a166244的博客
07-29 169
1.Hive表的基本数据类型 tinyint, smallint, int, bigint, boolean, float, double, string, binary, timestamp, decimal, char, varchar, date 2.Hive表的集合类型 Array：ARRAY类型是由一系列相同数据类型的元素组成, 这些元...
clickhouse外部表建表语句范例
最新发布
07-14
下面是一个建立ClickHouse外部表的示例DDL语句： ```sql CREATE TABLE external_table ( id Int32, name String, age UInt8 ) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'); ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值