元数据在MySQL中比较重要的几个表

Hive元数据管理深入解析

最新推荐文章于 2025-07-10 11:52:15 发布

AaronLwx

最新推荐文章于 2025-07-10 11:52:15 发布

阅读量435

点赞数

CC 4.0 BY-SA版权

分类专栏： HIve

本文链接：https://blog.youkuaiyun.com/xiaoxiongaa0/article/details/89400306

HIve 专栏收录该内容

6 篇文章

订阅专栏

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ruoze_d6           |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use ruoze_d6;
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_ruoze_d6        |
+---------------------------+
| BUCKETING_COLS            |
| CDS                       |
| COLUMNS_V2                |
| DATABASE_PARAMS           |
| DBS                       |
| FUNCS                     |
| FUNC_RU                   |
| GLOBAL_PRIVS              |
| PARTITIONS                |
| PARTITION_KEYS            |
| PARTITION_KEY_VALS        |
| PARTITION_PARAMS          |
| PART_COL_STATS            |
| ROLES                     |
| 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                      |
| VERSION                   |
+---------------------------+

重点关注下面几张表，我们一起来看一下里面都有什么

VERSION
DBS
TBLS
COLUMNS_V2
PARTITIONS
PARTITION_KEYS

mysql> select * from VERSION;
+--------+----------------+---------------------------------------+
| VER_ID | SCHEMA_VERSION | VERSION_COMMENT                       |
+--------+----------------+---------------------------------------+
|      1 | 1.1.0          | Set by MetaStore hadoop@172.31.36.130 |
+--------+----------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> select * from DBS;
+-------+-----------------------+-------------------------------------------+---------+------------+------------+
| DB_ID | DESC                  | DB_LOCATION_URI                           | NAME    | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+-------------------------------------------+---------+------------+------------+
|     1 | Default Hive database | hdfs://hadoop004:9000/user/hive/warehouse | default | public     | ROLE       |
+-------+-----------------------+-------------------------------------------+---------+------------+------------+
1 row in 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 |
+--------+-------------+-------+------------------+--------+-----------+-------+-------------------+----------------+--------------------+--------------------+
|      1 |  1555645523 |     1 |                0 | hadoop |         0 |     1 | hello             | EXTERNAL_TABLE | NULL               | NULL               |
|      6 |  1555654688 |     1 |                0 | hadoop |         0 |     6 | page_views        | MANAGED_TABLE  | NULL               | NULL               |
|      7 |  1555655459 |     1 |                0 | hadoop |         0 |     7 | page_views_snappy | MANAGED_TABLE  | NULL               | NULL               |
|     11 |  1555656266 |     1 |                0 | hadoop |         0 |    11 | page_views_bzip2  | MANAGED_TABLE  | NULL               | NULL               |
|     12 |  1555656499 |     1 |                0 | hadoop |         0 |    12 | page_views_gzip   | MANAGED_TABLE  | NULL               | NULL               |
+--------+-------------+-------+------------------+--------+-----------+-------+-------------------+----------------+--------------------+--------------------+
5 rows in set (0.00 sec)

mysql> select * from COLUMNS_V2;
+-------+---------+-------------+-----------+-------------+
| CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
+-------+---------+-------------+-----------+-------------+
|     1 | NULL    | id          | int       |           0 |
|     1 | NULL    | name        | string    |           1 |
|     6 | NULL    | city_id     | string    |           6 |
|     6 | NULL    | end_user_id | string    |           5 |
|     6 | NULL    | ip          | string    |           4 |
|     6 | NULL    | referer     | string    |           3 |
|     6 | NULL    | session_id  | string    |           2 |
|     6 | NULL    | track_times | string    |           0 |
|     6 | NULL    | url         | string    |           1 |
|     7 | NULL    | city_id     | string    |           6 |
|     7 | NULL    | end_user_id | string    |           5 |
|     7 | NULL    | ip          | string    |           4 |
|     7 | NULL    | referer     | string    |           3 |
|     7 | NULL    | session_id  | string    |           2 |
|     7 | NULL    | track_times | string    |           0 |
|     7 | NULL    | url         | string    |           1 |
|    11 | NULL    | city_id     | string    |           6 |
|    11 | NULL    | end_user_id | string    |           5 |
|    11 | NULL    | ip          | string    |           4 |
|    11 | NULL    | referer     | string    |           3 |
|    11 | NULL    | session_id  | string    |           2 |
|    11 | NULL    | track_times | string    |           0 |
|    11 | NULL    | url         | string    |           1 |
|    12 | NULL    | city_id     | string    |           6 |
|    12 | NULL    | end_user_id | string    |           5 |
|    12 | NULL    | ip          | string    |           4 |
|    12 | NULL    | referer     | string    |           3 |
|    12 | NULL    | session_id  | string    |           2 |
|    12 | NULL    | track_times | string    |           0 |
|    12 | NULL    | url         | string    |           1 |
+-------+---------+-------------+-----------+-------------+
30 rows in set (0.00 sec)

mysql> select * from SDS \G
*************************** 1. row ***************************
                    SD_ID: 1
                    CD_ID: 1
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop004:9000/hello
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 1
*************************** 2. row ***************************
                    SD_ID: 6
                    CD_ID: 6
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop004:9000/user/hive/warehouse/page_views
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 6
*************************** 3. row ***************************
                    SD_ID: 7
                    CD_ID: 7
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop004:9000/user/hive/warehouse/page_views_snappy
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 7
*************************** 4. row ***************************
                    SD_ID: 11
                    CD_ID: 11
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop004:9000/user/hive/warehouse/page_views_bzip2
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 11
*************************** 5. row ***************************
                    SD_ID: 12
                    CD_ID: 12
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop004:9000/user/hive/warehouse/page_views_gzip
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                 SERDE_ID: 12
5 rows in set (0.00 sec)

mysql> select * from CDS \G
*************************** 1. row ***************************
CD_ID: 1
*************************** 2. row ***************************
CD_ID: 6
*************************** 3. row ***************************
CD_ID: 7
*************************** 4. row ***************************
CD_ID: 11
*************************** 5. row ***************************
CD_ID: 12
5 rows in set (0.00 sec)

mysql> select * from PARTITIONS;
Empty set (0.00 sec)


mysql> select * from PARTITION_KEYS;
+--------+--------------+-----------+-----------+-------------+
| TBL_ID | PKEY_COMMENT | PKEY_NAME | PKEY_TYPE | INTEGER_IDX |
+--------+--------------+-----------+-----------+-------------+
|      1 | NULL         | day       | string    |           0 |
+--------+--------------+-----------+-----------+-------------+
1 row in set (0.00 sec)