#答案在此
mysql -uhive -p -hnamenode01
use hive;
#SQL语句如下
select
T5.NAME,
T1.TBL_NAME,
T2.PARAM_VALUE
from TBLS T1,
SERDE_PARAMS T2,
SDS T3,
DBS T5
WHERE T3.SERDE_ID=T2.SERDE_ID
AND T3.SD_ID=T1.SD_ID
AND T5.DB_ID=T1.DB_ID
AND T2.PARAM_KEY='field.delim'
AND T5.NAME='default'
AND T1.TBL_NAME='dim_other'
;
下面记录整体操作过程及mysql的变化情况
hive> create table dim_other
> (
> phone_no string,
> name string
> ) row format delimited
> fields terminated by '|'
> stored as textfile;
OK
Time taken: 0.207 seconds
hive> LOAD DATA LOCAL INPATH '/root/dual.txt' OVERWRITE INTO TABLE dim_other;
Loading data to table default.dim_other
Table default.dim_other stats: [numFiles=1, numRows=0, totalSize=22, rawDataSize=0]
OK
Time taken: 0.488 seconds
hive> select * from dim_other;
OK
15838162873 要树立
Time taken: 0.222 seconds, Fetched: 1 row(s)
hive> exit;
[root@cdh1 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2050
Server version: 5.6.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| amon |
| cm |
| hive |
| hue |
| mysql |
| oozie |
| performance_schema |
| rm |
| test |
+--------------------+
10 rows in set (0.00 sec)
mysql> use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_hive |
+---------------------------+
| BUCKETING_COLS |
| CDS |
| COLUMNS_V2 |
| COMPACTION_QUEUE |
| COMPLETED_TXN_COMPONENTS |
| DATABASE_PARAMS |
| DBS |
| DB_PRIVS |
| DELEGATION_TOKENS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| HIVE_LOCKS |
| IDXS |
| INDEX_PARAMS |
| MASTER_KEYS |
| NEXT_COMPACTION_QUEUE_ID |
| NEXT_LOCK_ID |
| NEXT_TXN_ID |
| NOTIFICATION_LOG |
| NOTIFICATION_SEQUENCE |
| NUCLEUS_TABLES |
| PARTITIONS |
| PARTITION_EVENTS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_PRIVS |
| PART_COL_STATS |
| PART_PRIVS |
| ROLES |
| ROLE_MAP |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
| SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES |
| SORT_COLS |
| TABLE_PARAMS |
| TAB_COL_STATS |
| TBLS |
| TBL_COL_PRIVS |
| TBL_PRIVS |
| TXNS |
| TXN_COMPONENTS |
| TYPES |
| TYPE_FIELDS |
| VERSION |
+---------------------------+
53 rows in set (0.00 sec)
mysql> select * from BUCKETING_COLS ;
Empty set (0.00 sec)
mysql> select * from CDS ;
+-------+
| CD_ID |
+-------+
| 347 |
+-------+
1 row in set (0.00 sec)
mysql> select * from COLUMNS_V2 ;
+-------+---------+-------------+-----------+-------------+
| CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
+-------+---------+-------------+-----------+-------------+
| 347 | NULL | name | string | 1 |
| 347 | NULL | phone_no | string | 0 |
+-------+---------+-------------+-----------+-------------+
2 rows in set (0.00 sec)
mysql> select * from COMPACTION_QUEUE ;
Empty set (0.00 sec)
mysql> select * from COMPLETED_TXN_COMPONENTS ;
Empty set (0.00 sec)
mysql> select * from DATABASE_PARAMS ;
Empty set (0.00 sec)
mysql> select * from DBS ;
+-------+-----------------------+--------------------------------------+---------+------------+------------+
| DB_ID | DESC | DB_LOCATION_URI | NAME | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+--------------------------------------+---------+------------+------------+
| 1 | Default Hive database | hdfs://cdh1:8020/user/hive/warehouse | default | public | ROLE |
+-------+-----------------------+--------------------------------------+---------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from DB_PRIVS ;
Empty set (0.00 sec)
mysql> select * from DELEGATION_TOKENS ;
Empty set (0.00 sec)
mysql> select * from FUNCS ;
Empty set (0.00 sec)
mysql> select * from FUNC_RU ;
Empty set (0.00 sec)
mysql> select * from GLOBAL_PRIVS ;
+---------------+-------------+--------------+---------+--------------+----------------+----------------+-----------+
| USER_GRANT_ID | CREATE_TIME | GRANT_OPTION | GRANTOR | GRANTOR_TYPE | PRINCIPAL_NAME | PRINCIPAL_TYPE | USER_PRIV |
+---------------+-------------+--------------+---------+--------------+----------------+----------------+-----------+
| 1 | 1551841258 | 1 | admin | ROLE | admin | ROLE | All |
+---------------+-------------+--------------+---------+--------------+----------------+----------------+-----------+
1 row in set (0.00 sec)
mysql> select * from HIVE_LOCKS ;
Empty set (0.00 sec)
mysql> select * from IDXS ;
Empty set (0.00 sec)
mysql> select * from INDEX_PARAMS ;
Empty set (0.00 sec)
mysql> select * from MASTER_KEYS ;
Empty set (0.00 sec)
mysql> select * from NEXT_COMPACTION_QUEUE_ID ;
+----------+
| NCQ_NEXT |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select * from NEXT_LOCK_ID ;
+---------+
| NL_NEXT |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> select * from NEXT_TXN_ID ;
+-----------+
| NTXN_NEXT |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from NOTIFICATION_LOG ;
Empty set (0.00 sec)
mysql> select * from NOTIFICATION_SEQUENCE ;
Empty set (0.00 sec)
mysql> select * from NUCLEUS_TABLES ;
Empty set (0.00 sec)
mysql> select * from PARTITIONS ;
Empty set (0.00 sec)
mysql> select * from PARTITION_EVENTS ;
Empty set (0.00 sec)
mysql> select * from PARTITION_KEYS ;
Empty set (0.00 sec)
mysql> select * from PARTITION_KEY_VALS ;
Empty set (0.00 sec)
mysql> select * from PARTITION_PARAMS ;
Empty set (0.00 sec)
mysql> select * from PART_COL_PRIVS ;
Empty set (0.00 sec)
mysql> select * from PART_COL_STATS ;
Empty set (0.00 sec)
mysql> select * from PART_PRIVS ;
Empty set (0.00 sec)
mysql> select * from ROLES ;
+---------+-------------+------------+-----------+
| ROLE_ID | CREATE_TIME | OWNER_NAME | ROLE_NAME |
+---------+-------------+------------+-----------+
| 1 | 1551841258 | admin | admin |
| 2 | 1551841258 | public | public |
+---------+-------------+------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from ROLE_MAP ;
Empty set (0.00 sec)
mysql> select * from SDS ;
+-------+-------+------------------------------------------+---------------+---------------------------+------------------------------------------------+-------------+------------------------------------------------------------+----------+
| SD_ID | CD_ID | INPUT_FORMAT | IS_COMPRESSED | IS_STOREDASSUBDIRECTORIES | LOCATION | NUM_BUCKETS | OUTPUT_FORMAT | SERDE_ID |
+-------+-------+------------------------------------------+---------------+---------------------------+------------------------------------------------+-------------+------------------------------------------------------------+----------+
| 1027 | 347 | org.apache.hadoop.mapred.TextInputFormat | | | hdfs://cdh1:8020/user/hive/warehouse/dim_other | -1 | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | 1027 |
+-------+-------+------------------------------------------+---------------+---------------------------+------------------------------------------------+-------------+------------------------------------------------------------+----------+
1 row in set (0.00 sec)
mysql> select * from SD_PARAMS ;
Empty set (0.00 sec)
mysql> select * from SEQUENCE_TABLE ;
+-----------------------------------------------------------+----------+
| SEQUENCE_NAME | NEXT_VAL |
+-----------------------------------------------------------+----------+
| org.apache.hadoop.hive.metastore.model.MColumnDescriptor | 351 |
| org.apache.hadoop.hive.metastore.model.MDatabase | 351 |
| org.apache.hadoop.hive.metastore.model.MGlobalPrivilege | 6 |
| org.apache.hadoop.hive.metastore.model.MPartition | 691 |
| org.apache.hadoop.hive.metastore.model.MRole | 6 |
| org.apache.hadoop.hive.metastore.model.MSerDeInfo | 1031 |
| org.apache.hadoop.hive.metastore.model.MStorageDescriptor | 1031 |
| org.apache.hadoop.hive.metastore.model.MTable | 351 |
+-----------------------------------------------------------+----------+
8 rows in set (0.00 sec)
mysql> select * from SERDES ;
+----------+------+----------------------------------------------------+
| SERDE_ID | NAME | SLIB |
+----------+------+----------------------------------------------------+
| 1027 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
+----------+------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from SERDE_PARAMS ;
+----------+----------------------+-------------+
| SERDE_ID | PARAM_KEY | PARAM_VALUE |
+----------+----------------------+-------------+
| 1027 | field.delim | | |
| 1027 | serialization.format | | |
+----------+----------------------+-------------+
2 rows in set (0.00 sec)
mysql> select * from SKEWED_COL_NAMES ;
Empty set (0.00 sec)
mysql> select * from SKEWED_COL_VALUE_LOC_MAP ;
Empty set (0.00 sec)
mysql> select * from SKEWED_STRING_LIST ;
Empty set (0.00 sec)
mysql> select * from SKEWED_STRING_LIST_VALUES ;
Empty set (0.00 sec)
mysql> select * from SKEWED_VALUES ;
Empty set (0.00 sec)
mysql> select * from SORT_COLS ;
Empty set (0.00 sec)
mysql> select * from TABLE_PARAMS ;
+--------+-----------------------+-------------+
| TBL_ID | PARAM_KEY | PARAM_VALUE |
+--------+-----------------------+-------------+
| 347 | COLUMN_STATS_ACCURATE | true |
| 347 | numFiles | 1 |
| 347 | numRows | 0 |
| 347 | rawDataSize | 0 |
| 347 | totalSize | 22 |
| 347 | transient_lastDdlTime | 1551943269 |
+--------+-----------------------+-------------+
6 rows in set (0.00 sec)
mysql> select * from TAB_COL_STATS ;
Empty set (0.00 sec)
mysql> select * from TBLS ;
+--------+-------------+-------+------------------+-------+-----------+-------+-----------+---------------+--------------------+--------------------+----------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER | RETENTION | SD_ID | TBL_NAME | TBL_TYPE | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT | LINK_TARGET_ID |
+--------+-------------+-------+------------------+-------+-----------+-------+-----------+---------------+--------------------+--------------------+----------------+
| 347 | 1551943262 | 1 | 0 | root | 0 | 1027 | dim_other | MANAGED_TABLE | NULL | NULL | NULL |
+--------+-------------+-------+------------------+-------+-----------+-------+-----------+---------------+--------------------+--------------------+----------------+
1 row in set (0.00 sec)
mysql> select * from TBL_COL_PRIVS ;
Empty set (0.00 sec)
mysql> select * from TBL_PRIVS ;
Empty set (0.00 sec)
mysql> select * from TXNS ;
Empty set (0.00 sec)
mysql> select * from TXN_COMPONENTS ;
Empty set (0.00 sec)
mysql> select * from TYPES ;
Empty set (0.00 sec)
mysql> select * from TYPE_FIELDS ;
Empty set (0.00 sec)
mysql> select * from VERSION ;
+--------+----------------+----------------------------+
| VER_ID | SCHEMA_VERSION | VERSION_COMMENT |
+--------+----------------+----------------------------+
| 1 | 1.1.0 | Hive release version 1.1.0 |
+--------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>