> For the complete documentation index, see [llms.txt](https://duoduo3369.gitbook.io/skill_issues/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://duoduo3369.gitbook.io/skill_issues/bigdata/hive.issue.md).

# 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
```