CDH6 Hive服务的元数据存储在Mysql,进入存储Hive元数据的Mysql数据库,修改DBS表和SDS表相应的值,如下所示。
说明 在进行元数据修改的时候,建议使用root用户,或者其他有权限的用户,避免因为权限问题导致修改失败。其中mysql服务的root用户密码是在搭建CDH服务时设置的密码。MySQL [(none)]> use cdh6hive ;
#修改表“DBS”中的数据
MySQL [cdh6hive]> select * from DBS ;
+-------+-----------------------+---------------------------------------------------------------------+--------------------------+------------+------------+
| DB_ID | DESC | DB_LOCATION_URI | NAME | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+---------------------------------------------------------------------+--------------------------+------------+------------+
| 1 | Default Hive database | hdfs://hadoop9:8020/user/hive/warehouse | default | public | ROLE |
| 13 | NULL | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db | analysis_logs | root | USER |
| 14 | NULL | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs_report.db | analysis_logs_report | root | USER |
| 15 | NULL | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs_report_old.db | analysis_logs_report_old | root | USER |
+-------+-----------------------+---------------------------------------------------------------------+--------------------------+------------+------------+
4 rows in set (0.00 sec)
MySQL [cdh6hive]> UPDATE DBS SET DB_LOCATION_URI = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse" WHERE DB_ID = 1 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE DBS SET DB_LOCATION_URI = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db " WHERE DB_ID = 13 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]>
MySQL [cdh6hive]> UPDATE DBS SET DB_LOCATION_URI = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs_report.db" WHERE DB_ID = 14 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]>
MySQL [cdh6hive]> UPDATE DBS SET DB_LOCATION_URI = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs_report_old.db" WHERE DB_ID = 15 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#修改表“SDS”中的数据
MySQL [cdh6hive]> select * from SDS ;
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+---------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
| SD_ID | CD_ID | INPUT_FORMAT | IS_COMPRESSED | IS_STOREDASSUBDIRECTORIES | LOCATION | NUM_BUCKETS | OUTPUT_FORMAT | SERDE_ID |
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+---------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
| 25 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 25 |
| 44 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=15 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 44 |
| 45 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=14 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 45 |
| 46 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=13 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 46 |
| 47 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=12 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 47 |
| 48 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=11 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 48 |
| 49 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=10 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 49 |
| 53 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=9 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 53 |
| 54 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | hdfs://hadoop9:8020/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=8 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 54 |
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+---------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
9 rows in set (0.00 sec)
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned" WHERE SD_ID = 25 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=15" WHERE SD_ID = 44 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=14" WHERE SD_ID = 45 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=13" WHERE SD_ID = 46 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=12" WHERE SD_ID = 47 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=11" WHERE SD_ID = 48 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=10" WHERE SD_ID = 49 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=9" WHERE SD_ID = 53 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> UPDATE SDS SET LOCATION = "dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=8" WHERE SD_ID = 54 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [cdh6hive]> select * from SDS ;
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
| SD_ID | CD_ID | INPUT_FORMAT | IS_COMPRESSED | IS_STOREDASSUBDIRECTORIES | LOCATION | NUM_BUCKETS | OUTPUT_FORMAT | SERDE_ID |
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
| 25 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 25 |
| 44 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=15 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 44 |
| 45 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=14 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 45 |
| 46 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=13 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 46 |
| 47 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=12 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 47 |
| 48 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=11 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 48 |
| 49 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=10 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 49 |
| 53 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=9 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 53 |
| 54 | 14 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat | | | dfs://f-xxxxxxxxxxxxxxx.cn-xxxxxxx.dfs.aliyuncs.com:10290/user/hive/warehouse/analysis_logs.db/original_log_bj_partitioned/year=2019/month=7/day=8 | -1 | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | 54 |
+-------+-------+---------------------------------------------------------------+---------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+----------------------------------------------------------------+----------+
9 rows in set (0.00 sec)