# hive

## 下载

```
https://archive.apache.org/dist/hive/

国内源
https://mirror.bit.edu.cn/apache/hive/
```

## 本地安装

解压后导入环境变量

```
export HIVE_HOME="/opt/projects/libs/apache-hive-2.3.7-bin"
export PATH="$PATH:$HIVE_HOME/bin"
```

<https://cwiki.apache.org/confluence/display/Hive/GettingStarted>

```
    export HADOOP_HOME=<hadoop-install-dir>

    $HADOOP_HOME/bin/hadoop fs -mkdir       /tmp
    $HADOOP_HOME/bin/hadoop fs -mkdir       /user/hive/warehouse
    $HADOOP_HOME/bin/hadoop fs -chmod g+w   /tmp
    $HADOOP_HOME/bin/hadoop fs -chmod g+w   /user/hive/warehouse
```

配置 metadata

```
    cp $HIVE_HOME/conf/hive-default.xml.template   $HIVE_HOME/conf/hive-site.xml
```

添加这几段

system:java.io.tmpdir/tmp/hive/javasystem:user.name${user.name}

修改 metadata 数据库到 mysql

javax.jdo.option.ConnectionURLjdbc:mysql://localhost:3306/hive\_metastore JDBC connect string for a JDBC metastore. To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL. For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.javax.jdo.option.ConnectionPasswordhivepasswordpassword to use against metastore databasejavax.jdo.option.ConnectionUserNamehiveUsername to use against metastore databasejavax.jdo.option.ConnectionDriverNamecom.mysql.jdbc.DriverDriver class name for a JDBC metastore

mysql 创建表

```
    CREATE USER 'hive'@'%' IDENTIFIED BY 'hivepassword';
    create database hive_metastore;
    GRANT ALL ON hive_metastore.* TO 'hive'@'%';
    flush privileges;
```

将正确版本的mysql-mysql-connector-java.jar 放到 $HIVE\_HOME/lib中

初始化metadata表

```
    schematool -dbType mysql -initSchema
```

观察是否安装正常

```
hive
```

## 使用外部表

当一个目录下有一堆相同类型的csv时, 似乎需要制定文件夹而不是某个csv，LOCATION默认是hdfs，与hdfs\:///testhive 类似

```
CREATE EXTERNAL TABLE IF NOT EXISTS hive_test(
      Id1 STRING, Id2 STRING, Id3 STRING, Name STRING)
      ROW FORMAT DELIMITED
      FIELDS TERMINATED BY ','
      STORED AS TEXTFILE
      LOCATION '/testhive';
```

这样可以直接select \*

## 拷贝一个表的内容

例如需要拷贝的表为 hive\_test

1. 观察表结构

   show create table hive\_test;
2. 将第一步的result表名字改成要拷贝的表名创建hive表, 例如 export\_hive\_test
3. 插入数据

   insert into export\_hive\_test select \* from hive\_test;

## bash执行hive短句

```
hive -S -e 'show create table hive_test'
```

## bash通过文件执行sql

```
hive -f /tmp/hive_test.sql
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://duoduo3369.gitbook.io/skill_issues/bigdata/hive.issue.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.