本文件所描述的內容屬於神策分析的高級使用功能，涉及較多技術細節，適用於對相關功能有經驗的用戶參考。如果對文件內容有疑惑，請諮詢您的數據諮詢顧問取得一對一的協助。

在神策分析的單機和叢集版中，我們提供了更加高效、穩定的 SQL 查詢方式，即直接使用 JDBC 或者 impala-shell 進行數據查詢。關於具體如何使用 JDBC 連接 Impala 可以直接參考官方文件。

1. 取得 JDBC 網址

登入任意的神策伺服器
切換至 sa_cluster 帳號

su - sa_cluster

CODE

使用以下指令取得網址

monitor_tools get_config -t client -m impala

BASH

spadmin config get client -m impala

BASH

例如輸出是：

{
	"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"
}

JS

其中，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>

XML

注意：實際程式碼執行中， Hive JDBC Driver 會依賴其他 jar 包，主要有 hadoop-common 、hive-service、hive-common 和 libthrift。正常這些 jar 包會自動取得，如果沒有自動取得，需要再增加相應的 jar 包。

另外，Impala 也支援使用官方的 Impala JDBC Driver 進行連接，不過為了兼容神策分析系統，請使用的時候務必開啟 Native SQL 的選項，例如：

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

CODE

注意：使用不同 Driver 連接時使用的 JDBC URI 也會有所不同。

2. 數據表結構

目前，神策分析的所有數據都映射到事件表（events）和用戶表（users）這兩張數據表，但連接JDBC 進行數據連接時，會顯示`event_ros_p*`、`event_view_p*` 等底層表，查詢數據不需要關注這些底層表，按照此文件下方的例子，使用事件表（events）和用戶表（users）即可查詢所有的事件數據和用戶數據。表結構可參考文件 [數據表]。

具體專案對應的表結構，也可在神策分析的自定義查詢中查看：

3. 使用 impala-shell 進行查詢

除了直接使用 JDBC 介面之外，也可以直接使用 impala-shell 工具進行查詢。通常有兩種使用方式：

直接登入任意的神策伺服器，執行 impala-shell 指令即可。
使用任意 2.6.0 以上的 impala-shell 用戶端，連接到上面 hive_url_list 中的網址（無需指定埠號）。
神策使用的庫名是 rawdata, 注意切換過去後再做查詢或匯出數據。

4. 常規使用

為了區分查詢神策的數據與一般的 Impala 數據，需要在 SQL 中使用特殊的註解來進行標識，例如查詢預設專案的 events 數據：

SELECT user_id,distinct_id,event,time,$lib as lib  FROM events WHERE `date` = CURRENT_DATE() LIMIT 10 /*SA*/;

SQL

其中的 `/*SA*/` 表示目前 SQL 是一個發給神策系統的查詢。類似的，如果想看 events 表有哪些欄位，可以使用：

DESC events /*SA*/;

SQL

如果不是查詢預設專案，則需要指定專案名稱，例如：

SELECT id,first_id,second_id  FROM users LIMIT 10 /*SA(test_project)*/;

SQL

最後，我們還可以讓一個 SQL 的一部分使用神策的查詢，其它部分使用正常的 Impala 查詢，例如：

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

SQL

使用這種方式，也可以很容易的實作把神策的數據和其它外部數據表進行 JOIN。

特別注意，如果您的專案開啟了多對一用戶關聯，如果不加 /*+remapping_on*/ 註釋，預設匯出的是未經過多對一修復後的數據。如果需要匯出多對一修復後的數據，先要確保您的 impala 版本大於等於 3.2.0.069（可諮詢神策值班同學確認），然後在查詢匯出語句增加 /*+remapping_on*/ 註釋，例如

SELECT * from events limit 1 /*SA(default)*/ /*+remapping_on*/

SQL

5. 數據匯出

如果想把神策的數據匯出成文字格式，用於備份或者其它用途，那可以使用以下方案：

建立一個文字格式的數據表，把待匯出的數據插入此表。

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*/

SQL

注意：普通 Impala/Hive 表不支援帶 $ 的欄位，因此如果匯出這類欄位需要使用 AS 重命名。

取得該數據表的 HDFS 路徑

SHOW TABLE STATS default.export_data

SQL

其中輸出的 Location 即是匯出檔案所在的 HDFS 目錄，例如:

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

CODE

以使用 hadoop 指令將 HDFS 檔案拿到本地：

hadoop fs -get hdfs://data01:8020/user/hive/warehouse/export_data/

CODE

上述目錄裡的文件是以 Hive 預設分隔符（即 \001）進行列分隔的文字文件。
按需複製走上面路徑下的文件即可。

6. 和 Spark 整合

這裡以 Python API 為例，使用 Spark 的 JDBC Connector 直接連接神策的原始數據，Driver 使用 2.6.3 或以上版本：

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()

PY

也可以使用 spark-shell 執行的同樣的例子，注意要把對應的 Driver 加載進來，Driver 使用 2.6.3 或以上版本：

spark-shell --driver-class-path ImpalaJDBC41.jar --jars ImpalaJDBC41.jar

BASH

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

SCALA

注意：該方式僅在 Spark 2.2 版本測試通過。另外，這種方式會使用單執行緒方式從 Impala 取得數據，因此不適用於 SQL 會回傳大量數據內容的方式，請盡量用 Impala SQL 做完前處理之後再引入 Spark 進行後續處理。