使用 JDBC 进行数据访问

最后更新于:2019-05-16 14:55:48

本文档所描述的内容属于神策分析的高级使用功能,涉及较多技术细节,适用于对相关功能有经验的用户参考。如果对文档内容有疑惑,请咨询您的数据咨询顾问获取一对一的协助。

在神策分析的单机和集群版中,我们提供了更加高效、稳定的 SQL 查询方式,即直接使用 JDBC 或者 impala-shell 进行数据查询。关于具体如何使用 JDBC 连接 Impala 可以直接参考官方文档

1. 获取 JDBC 地址

  • 登录任意的神策服务器

  • 切换至 sa_cluster 账户

    su - sa_cluster
    
  • 使用以下命令获取地址

  monitor_tools get_config -t client -m impala
  spadmin config get client -m impala

例如输出是

  {
      "hive_url_list": [
          "jdbc:hive2://192.168.1.2:21050/rawdata;auth=noSasl",
          "jdbc:hive2://192.168.1.3:21050/rawdata;auth=noSasl",
      ],
      "hive_user": "sa_cluster"
  }

其中,hive_url_list 中的任意一个地址都可用于连接。

如果使用代码访问,我们建议使用 1.1.0 版本的 Hive JDBC Driver 来进行访问,Maven 的依赖定义如下:

    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.1.0</version>
    </dependency>

另外,Impala 也支持使用官方的 Impala Jdbc Drvier 进行访问,不过为了兼容神策分析系统,请使用的时候务必开启 Native SQL 的选项,例如:

```
jdbc:impala://192.168.1.1:21050/rawdata;UseNativeQuery=1
```

注意:使用不同 Driver 访问时使用的 JDBC URI 也会有所不同。

2. 使用 impala-shell 进行查询

除了直接使用 JDBC 接口之外,也可以直接使用 impala-shell 工具进行查询。通常有两种使用方式:

  1. 直接登录任意的神策服务器,运行 impala-shell 命令即可。
  2. 使用任意 2.6.0 以上的 impala-shell 客户端,连接到上面 hive_url_list 中的地址(无需指定端口)。
  3. 神策使用的库名是 rawdata, 注意切换过去后再做查询或导出数据。

3. 常规使用

为了区分查询神策的数据与一般的 Impala 数据,需要在 SQL 中使用特殊的注解来进行标识,例如查询默认项目的 events 数据:

SELECT * FROM events WHERE `date` = CURRENT_DATE() LIMIT 10 /*SA*/;

其中的 /*SA*/ 表示当前 SQL 是一个发给神策系统的查询。类似的,如果想看 events 表有哪些字段,可以使用:

DESC events /*SA*/;

如果不是查询默认项目,则需要指定项目名称,例如:

SELECT * FROM users LIMIT 10 /*SA(test_project)*/;

最后,我们还可以让一个 SQL 的一部分使用神策的查询,其它部分使用正常的 Impala 查询,例如:

CREATE TABLE test_data AS 
/*SA_BEGIN(test_project)*/ SELECT id, first_id, $city AS city FROM users LIMIT 10 /*SA_END*/

使用这种方式,也可以很容易的实现把神策的数据和其它外部数据表进行 JOIN。

4. 数据导出

如果想把神策的数据导出成文本格式,用于备份或者其它用途,那可以使用以下方案:

  • 创建一个文本格式的数据表,把待导出的数据插入此表。

    CREATE TABLE default.export_data AS 
    /*SA_BEGIN(production)*/
    SELECT user_id,time,event, $os AS _os FROM events WHERE date=CURRENT_DATE() LIMIT 10
    /*SA_END*/
    

    注意: 普通 Impala/Hive 表不支持带 $ 的字段,因此如果导出这类字段需要使用 AS 重命名。

  • 获取该数据表的 HDFS 路径

    SHOW TABLE STATS default.export_data
    

    其中输出的 Location 列即是导出文件所在的 HDFS 目录,例如:

    hdfs://data01:8020/user/hive/warehouse/export_data/
    

    可以使用 hadoop 命令将 HDFS 文件取到本地:

    hadoop fs -get hdfs://data01:8020/user/hive/warehouse/export_data/
    
  • 上述目录里的文件是以 Hive 默认分隔符(即 \001)进行列分隔的文本文件。

  • 按需拷贝走上面路径下的文件即可。

5. 和 Spark 集成

注意:和 Spark 集成需要使用 Impala Jdbc Drvier,不支持 Hive Driver,请下载对应的 jar 并放到 classpath 中。

这里以 Python API 为例,使用 Spark 的 JDBC Connector 直接访问神策的原始数据:

from pyspark.sql import SparkSession

jdbc_url= "jdbc:impala://localhost:21050/rawdata;UseNativeQuery=1"
spark = SparkSession.builder.appName("sa-test").getOrCreate()
df = spark.read.jdbc(url=jdbc_url, table="(/*SA(default)*/ SELECT date, event, count(*) AS c FROM events WHERE date=CURRENT_DATE() GROUP BY 1,2) a")
df.select(df['date'], df['event'], df['c'] * 10000).show()

也可以使用 spark-shell 执行的同样的例子,注意要把对应的 Driver 加载进来:

spark-shell --driver-class-path ImpalaJDBC41.jar --jars ImpalaJDBC41.jar
var test_jdbc = spark.sqlContext.read.format("jdbc").option("url", "jdbc:impala://localhost:21050/rawdata;UseNativeQuery=1").option("driver", "com.cloudera.impala.jdbc41.Driver").option("dbtable", "(/*SA(default)*/ SELECT date, event, count(*) AS c FROM events WHERE date=CURRENT_DATE() GROUP BY 1,2) a").load();
test_jdbc.show

注意: 该方式仅在 Spark 2.2 版本测试通过。另外,这种方式会使用单线程的方式从 Impala 获取数据,因此不适用于 SQL 会返回大量数据内容的方式,请尽量用 Impala SQL 做完预处理之后再引入 Spark 进行后续处理。