创建hive表并插入一条记录后mysql元数据库的变化

• 前言，为了研究hive元数据库的变化，做了个新的集群，然后做了一张表，然后插入1条记录，观察mysql的变化情况，由此得到了很多有用的结论，例如如何查到这张表的分隔符是什么。

• 上面疑问语句的答案是什么呢？

#答案在此
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>