Hive教程之metastore的三种模式

Hive中metastore（元数据存储）的三种方式：

• 内嵌Derby方式
• Local方式
• Remote方式

[一]、内嵌Derby方式

这个是Hive默认的启动模式，一般用于单元测试，这种存储方式有一个缺点：在同一时间只能有一个进程连接使用数据库。

hive-site.xml 中jdbc URL、驱动、用户名、密码等的配置信息如下：

1	<property>

2	<name>javax.jdo.option.ConnectionURL</name>

3	<value>jdbc:derby:;databaseName=metastore_db;create=true</value>

4	<description>JDBC connect string for a JDBC metastore</description>

5	</property>

6

7	<property>

8	<name>javax.jdo.option.ConnectionDriverName</name>

9	<value>org.apache.derby.jdbc.EmbeddedDriver</value>

10	<description>Driver class name for a JDBC metastore</description>

11	</property>

12

13	<property>

14	<name>javax.jdo.option.ConnectionUserName</name>

15	<value>APP</value>

16	<description>username to use against metastore database</description>

17	</property>

18

19	<property>

20	<name>javax.jdo.option.ConnectionPassword</name>

21	<value>mine</value>

22	<description>password to use against metastore database</description>

23	</property>

24

25	<property>

26	<name>hive.metastore.warehouse.dir</name>

27	<value>file:///Users/micmiu/tmp/hive/warehouse</value>

28	<description>unit test data goes in here on your local filesystem</description>

29	</property>

30

31	<!-- micmiu.com -->

执行初始化命令：schematool -dbType derby -initSchema

查看初始化后的信息： schematool -dbType derby -info

配置完成后就可在shell中以CLI的方式访问hive 进行操作验证。

[二]、Local方式

以本地Mysql数据库为例：创建好用户：hive；database：hive。

配置文件 hive-site.xml 中jdbc URL、驱动、用户名、密码等属性值配置如下：

1	<property>

2	<name>javax.jdo.option.ConnectionURL</name>

3	<value>jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true</value>

4	<description>JDBC connect string for a JDBC metastore</description>

5	</property>

6

7	<property>

8	<name>javax.jdo.option.ConnectionDriverName</name>

9	<value>com.mysql.jdbc.Driver</value>

10	<description>Driver class name for a JDBC metastore</description>

11	</property>

12

13	<property>

14	<name>javax.jdo.option.ConnectionUserName</name>

15	<value>hive</value>

16	<description>username to use against metastore database</description>

17	</property>

18

19	<property>

20	<name>javax.jdo.option.ConnectionPassword</name>

21	<value>micmiu</value>

22	<description>password to use against metastore database</description>

23	</property>

24

25	<property>

26	<name>hive.metastore.warehouse.dir</name>

27	<!-- base hdfs path -->

28	<value>/user/hive/warehouse</value>

29	<description>location of default database for the warehouse</description>

30	</property>

31	<!-- micmiu.com -->

ps：需要把mysql的驱动包copy到目录 <HIVE_HOME>/lib 中

如果是第一次需要执行初始化命令：schematool -dbType mysql -initSchema

1	micmiu-mbp:mysql micmiu$ schematool -dbType mysql -initSchema

2	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive

3	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.max.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.maxsize

4	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize

5	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.min.split.size.per.rack is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.rack

6	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node

7	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces

8	14/02/17 14:46:08 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative

9	Metastore connection URL:    jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true

10	Metastore Connection Driver :    com.mysql.jdbc.Driver

11	Metastore connection User:   hive

12	Starting metastore schema initialization to 0.12.0

13	Initialization script hive-schema-0.12.0.mysql.sql

14	Initialization script completed

15	schemaTool completeted

查看初始化后信息 schematool -dbType mysql -info

初始化后查看mysql中表情况：show tables;

1	mysql> show tables;

2	+---------------------------+

3	| Tables_in_hive            |

4	+---------------------------+

5	| BUCKETING_COLS            |

6	| CDS                       |

7	| COLUMNS_V2                |

8	| DATABASE_PARAMS           |

9	| DBS                       |

10	| DB_PRIVS                  |

11	| DELEGATION_TOKENS         |

12	| GLOBAL_PRIVS              |

13	| IDXS                      |

14	| INDEX_PARAMS              |

15	| MASTER_KEYS               |

16	| NUCLEUS_TABLES            |

17	| PARTITIONS                |

18	| PARTITION_EVENTS          |

19	| PARTITION_KEYS            |

20	| PARTITION_KEY_VALS        |

21	| PARTITION_PARAMS          |

22	| PART_COL_PRIVS            |

23	| PART_COL_STATS            |

24	| PART_PRIVS                |

25	| ROLES                     |

26	| ROLE_MAP                  |

27	| SDS                       |

28	| SD_PARAMS                 |

29	| SEQUENCE_TABLE            |

30	| SERDES                    |

31	| SERDE_PARAMS              |

32	| SKEWED_COL_NAMES          |

33	| SKEWED_COL_VALUE_LOC_MAP  |

34	| SKEWED_STRING_LIST        |

35	| SKEWED_STRING_LIST_VALUES |

36	| SKEWED_VALUES             |

37	| SORT_COLS                 |

38	| TABLE_PARAMS              |

39	| TAB_COL_STATS             |

40	| TBLS                      |

41	| TBL_COL_PRIVS             |

42	| TBL_PRIVS                 |

43	| TYPES                     |

44	| TYPE_FIELDS               |

45	| VERSION                   |

46	+---------------------------+

47	41 rows in set (0.00 sec)

配置完成后就可在shell中以CLI的方式访问hive 进行操作验证。

[三]、Remote方式

以Mysql数据库(192.168.6.77)为例：创建好用户：hive；database：hive_meta。Remote方式需要分别配置服务端和客户端的配置文件：

服务端的 hive-site.xml 中jdbc URL、驱动、用户名、密码等属性值配置如下：

1	<property>

2	<name>javax.jdo.option.ConnectionURL</name>

3	<value>jdbc:mysql://192.168.6.77/hive_meta?createDatabaseIfNotExist=true</value>

4	<description>JDBC connect string for a JDBC metastore</description>

5	</property>

6

7	<property>

8	<name>javax.jdo.option.ConnectionDriverName</name>

9	<value>com.mysql.jdbc.Driver</value>

10	<description>Driver class name for a JDBC metastore</description>

11	</property>

12

13	<property>

14	<name>javax.jdo.option.ConnectionUserName</name>

15	<value>hive</value>

16	<description>username to use against metastore database</description>

17	</property>

18

19	<property>

20	<name>javax.jdo.option.ConnectionPassword</name>

21	<value>micmiu</value>

22	<description>password to use against metastore database</description>

23	</property>

24

25	<property>

26	<name>hive.metastore.warehouse.dir</name>

27	<!-- base hdfs path -->

28	<value>/user/hive/warehouse</value>

29	<description>base hdfs path :location of default database for the warehouse</description>

30	</property>

31	<!-- micmiu.com -->

ps：需要把mysql的驱动包copy到目录 <HIVE_HOME>/lib 中

如果是第一次需要执行初始化命令：schematool -dbType mysql -initSchema

客户端中配置内容修改如下：

1	<!-- thrift://<host_name>:<port> 默认端口是9083 -->

2	<property>

3	<name>hive.metastore.uris</name>

4	<value>thrift://192.168.6.77:9083</value>

5	<description>Thrift uri for the remote metastore. Used by metastore client to connect to remote metastore.</description>

6	</property>

7

8	<!--  hive表的默认存储路径 -->

9	<property>

10	<name>hive.metastore.warehouse.dir</name>

11	<value>/user/hive/warehouse</value>

12	<description>location of default database for the warehouse</description>

13	</property>

hive metastore 服务端启动命令：
hive --service metastore -p <port_num>
如果不加端口默认启动：hive --service metastore，则默认监听端口是：9083 ，注意客户端中的端口配置需要和启动监听的端口一致。服务端启动正常后，客户端就可以执行hive操作了。