CData API Serverで作成したAPIでDirectqueryを使ってみる
はじめに
データアナリティクス事業本部のkobayashiです。
REST API構築ツール CData API Server でデータソースを設定する際にダイレクトクエリを有効にするとリソースを設定しなくてもエンドポイントが作成されデータソースでSQLクエリを実行した結果をAPI経由で取得することができます。クライアントへデータソースへ直接接続させるのが困難な場合でも自由にクエリを実行させたい場合に使う、いわばデータソースへの接続をCData API Serverでラップしたイメージとなります。 今回はこの機能を試してみたいと思います。
なお、無償版のCData API Serverでダイレクトクエリを使おうとするとThe current license for this application does not allow direct queries. Please contact [email protected] to enable this feature.とレスポンスが返ってきます。ダイレクトクエリを試してみたい場合は無償版ではなくトライアル版をご利用ください。
CData API Serverのインストール方法やデータソースの設定方法は下記のエントリをご参照ください。
環境
- Windows 10
- CData API Server - 19.0.7362.0
- データソース
- MariaDB 10.5
- Redshift
- Snowflake
また今回使用しているCData API Serverのホストとポートは以下になります。
192.168.7.129:8153
ダイレクトクエリを有効にする
ダイレクトクエリを有効にするためには
- データソースの設定
- ユーザの設定
をダイレクトクエリ対応に変更する必要があります。
1.データソースの設定でAllow Direct Queriesにチェックし、変更を保存を押下する。
この設定を行うだけで下図の様に接続名でダイレクトクエリ用のエンドポイントが作成されます。
2.ユーザ設定を変更して変更を保存を押下する
POSTにチェック- ダイレクトクエリはPOSTメソッドを使用してクエリ文字列を送信するために必要になります。
ダイレクトクエリを許可をチェック
ダイレクトクエリを使用してデータ取得
ダイレクトクエリを使うためにはデータソースに接続した際に作成されたエンドポイントに対してPOSTメソッドでリクエストします。
メッセージボディのqueryは必須ですが、parametersはなくても問題ありません。
// エンドポイント
http://192.168.7.129:8153/query.rsc/Conn7
// メッセージボディ
{
"query": "select * FROM world.country WHERE continent=@continent AND population >= @population",
"parameters": "@continent='Asia',@population=1000000000" //省略可
}
では試しにいくつかダイレクトクエリ機能を使ってみます。
URL1
データソースConn7(MariaDB)に対して、worldスキーマのcountryテーブルからcontinentがAsiaで且つpopulationが1,000,000,000以上のデータを取得
// エンドポイント
http://192.168.7.129:8153/query.rsc/Conn7
// メッセージボディ
{
"query": "select * FROM world.country WHERE continent=@continent AND population > @population",
"parameters": "@continent='Asia',@population=1000000000"
}
レスポンス1
指定したクエリのレスポンスが取得できます。
{
"items": [
{
"code": "CHN",
"name": "China",
"continent": "Asia",
"region": "Eastern Asia",
"surfacearea": "9572900.00",
"indepyear": "-1523",
"population": "1277558000",
"lifeexpectancy": "71.4",
"gnp": "982268.00",
"gnpold": "917719.00",
"localname": "Zhongquo",
"governmentform": "People'sRepublic",
"headofstate": "Jiang Zemin",
"capital": "1891",
"code2": "CN"
},
{
"code": "IND",
"name": "India",
"continent": "Asia",
"region": "Southern and Central Asia",
"surfacearea": "3287263.00",
"indepyear": "1947",
"population": "1013662000",
"lifeexpectancy": "62.5",
"gnp": "447114.00",
"gnpold": "430572.00",
"localname": "Bharat/India",
"governmentform": "Federal Republic",
"headofstate": "Kocheril Raman Narayanan",
"capital": "1109",
"code2": "IN"
}
]
}
URL2
データソースConn5(Redshift)のデータを取得
// エンドポイント
http://192.168.7.129:8153/query.rsc/Conn5
// メッセージボディ
{
"query": "SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc limit 100;",
}
レスポンス2
指定したクエリのレスポンスが取得できます。
{
"items": [
{
"firstname": "Jerry",
"lastname": "Nichols",
"total_quantity": "67"
},
{
"firstname": "Armando",
"lastname": "Lopez",
"total_quantity": "64"
},
{
"firstname": "Kameko",
"lastname": "Bowman",
"total_quantity": "64"
},
...
]
}
URL3
データソースConn5(Snowflake)のデータを取得
// エンドポイント
http://192.168.7.129:8153/query.rsc/Conn1
// メッセージボディ
{
"query": "select WEATHER as CONDITIONS
,count(*) as NUM_TRIPS
from DEMO_DB.PUBLIC.TRIPS
left outer join JSON_WEATHER_DATA_VIEW
on date_trunc('hour', OBSERVATION_TIME) = date_trunc('hour', STARTTIME)
where WEATHER is not null
group by 1 order by 2 desc limit 100;",
}
レスポンス3
指定したクエリのレスポンスが取得できます。
{
"items": [
{
"conditions": "Clear",
"num_trips": "9249531"
},
{
"conditions": "Clouds",
"num_trips": "8934964"
},
{
"conditions": "Rain",
"num_trips": "3206720"
},
...
]
}
注意点
ダイレクトクエリを検証している中で2点ハマった箇所と1点注意点があります。
ダイレクトクエリを使った場合と使わなかった場合でレスポンスの形が変わる
ダイレクトクエリを使った場合はレスポンスのitemsキーの中にデータが格納されていますが、使わない通常の場合はvalueキーの中にデータが入っているので注意しましょう。
Snowflakeの場合は識別子の形に注意する
はじめSnowflakeでダイレクトクエリを使っても全くデータが取得できなかったのでエラーメッセージを読むときちんと理由が書かれていました。 理由はテーブル名を小文字にしていたためで、Snowflakeのwebコンソールですと問題なく結果が返ってくるSQLでもCData API Serverのダイレクトクエリではエラーになるので注意しましょう。
SQL compilation error: Database "trips" does not exist or not authorized. Hint: Snowflake identifiers are case-sensitive. Please make sure that the catalog, schema, table and procedure names are spelled in the correct case.
ユーザーにある程度制限を掛ける必要がある
ダイレクトクエリはその名の通りデータソースでユーザーが作成したクエリを実行するため何かしら制限が必要だと感じました。制限をかけるためにCData API Serverの機能で以下のものを使うことを念頭に置いたほうが良いと思います。
まとめ
CData API Serverでダイレクトクエリを使うとクライアントサイドで柔軟にデータが取得できます。APIエンドポイントを作成するだけでクライアント側で自由にデータを取得できるのは面白い機能だと思いました。ある程度自由にデータソースを触ってもらいたいけど直接接続させるのが難しい場合は非常に有効です。
最後まで読んで頂いてありがとうございました。
