SparkSQL和Alluxio结合使用

ゞ浴缸里的玫瑰 2021-09-28 23:50 489阅读 0赞

### 1.先在hive配置文件中增加alluxio相关配置，然后再安装hive ###

修改配置文件 playbooks/hive/vars.yml:

#alluxio
    alluxio_path: "/opt/alluxio-1.8.1"
    alluxio_zookeeper_address: "192.168.6.12:2181,192.168.6.13:2181,192.168.6.14:2181"

\# hive configration增加

hive_site_properties:
     - {
        "name":"fs.alluxio.impl",
        "value":"alluxio.hadoop.FileSystem"
      }
      - {
        "name":"hive.metastore.schema.verification",
        "value":"false"
      }
      - {
        "name":"alluxio.zookeeper.enabled",
        "value":"true"
      }
      - {
        "name":"alluxio.zookeeper.address",
        "value":"{
       { alluxio_zookeeper_address }}"
      }

修改配置文件 roles/hive/templates/hive\_env.sh:

export HIVE_AUX_JARS_PATH={
       { alluxio_path }}/client/alluxio-1.8.1-client.jar:$HIVE_AUX_JARS_PATH
    export CLASSPATH=$CLASSPATH.:$HIVE_HOME/lib:$HIVE_AUX_JARS_PATH

### 2.准备测试数据 ###

[https://grouplens.org/datasets/movielens/][https_grouplens.org_datasets_movielens]

bin/alluxio fs mkdir /ml-100k
    bin/alluxio fs copyFromLocal /opt/ml-100k/u.user alluxio://192.168.6.12:19998/ml-100k

### 3.启动spark thriftserver ###

sbin/start-thriftserver.sh

### 4.使用beeline测试 ###

beeline
    !connect jdbc:hive2://192.168.6.12:10000/default
    hadoop #username 
    hadoop #password

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70][]

用hadoop连接成功后，先创建表再查询：

内部表
    CREATE TABLE u_user (
    userid INT,
    age INT,
    gender CHAR(1),
    occupation STRING,
    zipcode STRING)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
    LOCATION 'alluxio://192.168.6.12:19998/ml-100k';

查询：

select \* from u\_user limit 10;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 1][]

统计查询：

select count(\*) from u\_user;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 2][]

查看表结构、分组查询：

desc u\_user;

select avg(age),gender from u\_user group by gender;

select count(\*),gender from u\_user group by gender;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 3][]

创建外部表

CREATE EXTERNAL TABLE u_user_e (
    userid INT,
    age INT,
    gender CHAR(1),
    occupation STRING,
    zipcode STRING)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
    LOCATION 'alluxio://192.168.6.12:19998/ml-100k';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 4][]

[https_grouplens.org_datasets_movielens]: https://grouplens.org/datasets/movielens/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70]: /images/20210923/cca0d2b94d8b4cd3b2b67d722caae7a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 1]: /images/20210923/f4a6cb5da9354c12b178ea8ff4522fe6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 2]: /images/20210923/bf5264a85d944ee284a4e101c55ee2f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 3]: /images/20210923/5df8f4eacb18489fa262603dc552414d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 4]: /images/20210923/e95cb3f4d6eb42d0bad0e17872ab0505.png