SparkSQL和Alluxio环境配置和测试

谁践踏了优雅 2021-09-28 15:36 392阅读 0赞

# 1. 修改配置文件： #

## 1.1 修改alluxio的配置文件 ##

roles/install-alluxio/templates/alluxio-site.properties.j2：

alluxio.zookeeper.enabled=true
    
    alluxio.zookeeper.address={
       { groups['zookeeper'][0] }}:2181,{
       { groups['zookeeper'][1] }}:2181,{
       { groups['zookeeper'][2] }}:2181

## 1.2 修改spark的配置文件 ##

playbooks/spark/vars.yml:

增加alluxio配置

#alluxio
    alluxio_path: "/opt/alluxio-1.8.1"
    spark_driver_path: "{
       { alluxio_path }}/client/alluxio-1.8.1-client.jar"
    spark_executor_path: "{
       { alluxio_path }}/client/alluxio-1.8.1-client.jar"
    alluxio_zookeeper_address: "192.168.6.12:2181,192.168.6.13:2181,192.168.6.14:2181"

\#spark\_properties部分增加：

spark\_properties：

- {
          "name":"spark.driver.extraClassPath",
          "value":"{
       { spark_driver_path }}"
      }
    - {
          "name":"spark.executor.extraClassPath",
          "value":"{
       { spark_executor_path }}"
      }
    - {
          "name":"spark.driver.extraJavaOptions",
          "value":"-Dalluxio.zookeeper.address=192.168.6.12:2181,192.168.6.13:2181,192.168.6.14:2181 -Dalluxio.zookeeper.enabled=true"
      }
    - {
          "name":"spark.executor.extraJavaOptions",
          "value":"-Dalluxio.zookeeper.address=192.168.6.12:2181,192.168.6.13:2181,192.168.6.14:2181 -Dalluxio.zookeeper.enabled=true"
      }

## 1.3 修改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 }}"
      }

## 1.4 修改hive配置文件 ##

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/e9262cb1af6347ab8864786cc56f0887.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 1]: /images/20210923/8d7037a17a214b729bafb61c7319814d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 2]: /images/20210923/b7ede329fcbe4fb08111aef92a5750e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 3]: /images/20210923/3fb8faf44a5448bc8f3c779826f0e62d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3YWh1dA_size_16_color_FFFFFF_t_70 4]: /images/20210923/ab348d2f46a4499ca393222239101985.png