Spark SQL で CSV ファイルを処理2 - GeoLite2
前回の 「Spark SQL で CSV ファイルを処理 - GeoLite Legacy」 に続き、今回は Spark SQL を使って GeoLite2 City CSV ファイルを処理してみます。
今回のソースは http://github.com/fits/try_samples/tree/master/blog/20141112/
はじめに
GeoLite2 City の CSV は下記のような 2種類のファイルで構成しています。
GeoLite2-City-Blocks.csv で IP アドレスから geoname_id を割り出し、GeoLite2-City-Locations.csv で geoname_id から国・都市を特定します。
ファイルの内容は下記のようになっており、IP は IPv6 の形式で記載されています。
GeoLite2-City-Blocks.csv の例
network_start_ip,network_prefix_length,geoname_id,registered_country_geoname_id,represented_country_geoname_id,postal_code,latitude,longitude,is_anonymous_proxy,is_satellite_provider ・・・ ::ffff:1.0.64.0,114,1862415,1861060,,,・・・ ・・・ 2602:30a:2c1d::,48,5368361,,,・・・ ・・・
GeoLite2-City-Locations.csv の例
geoname_id,continent_code,continent_name,country_iso_code,country_name,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone 1862415,AS,Asia,JP,Japan,34,Hiroshima,・・・ ・・・
Spark SQL を使って IP アドレスから都市判定
GeoLite Legacy の Country CSV を処理した前回との違いは、下記 2点です。
- (1) GeoLite2-City-Blocks.csv と GeoLite2-City-Locations.csv の 2つの CSV を geoname_id で join する
- (2) network_start_ip と network_prefix_length を使って IP アドレスの数値の範囲を算出する
(1) は前回と同様に CSV を処理して SQL で join するだけです。 (2) は下記のようにして求める事ができます。
- (a) IP アドレスの開始値は network_start_ip を数値化
- (b) IP アドレスの終了値は (a) の値の下位
128 - network_prefix_lengthビットを全て 1 とした値
今回は IPv4 のみを対象とするため、GeoLite2-City-Blocks.csv の ::ffff: で始まる行だけを使って （::ffff: 以降がそのまま IPv4 に該当）、上記 (a) と (b) の処理を実装してみました。
注意点として、GeoLite2-City-Locations.csv には subdivision_iso_code 以降が全て空欄のデータも含まれていました。 （例えば 2077456,OC,Oceania,AU,Australia,,,,, を split(",") すると Array(2077456, OC, Oceania, AU, Australia) となってしまいます）
GetCity.scala
import org.apache.spark.SparkContext import org.apache.spark.sql.SQLContext import java.net.InetAddress // GeoLite2-City-Blocks.csv 用のスキーマ定義 case class IpMapping(startIpNum: Long, endIpNum: Long, geonameId: String) // GeoLite2-City-Locations.csv 用のスキーマ定義 case class City(geonameId: String, country: String, city: String) object GetCity extends App { if (args.length < 1) { println("<ip address>") System.exit(0) } // IPv4 の数値変換 val toIpNum = (ip: String) => Integer.toUnsignedLong(InetAddress.getByName(ip).hashCode()) val locationFile = "GeoLite2-City-Locations.csv" val blockFile = "GeoLite2-City-Blocks.csv" val sc = new SparkContext("local", "GetCity") val sqlContext = new SQLContext(sc) import sqlContext.createSchemaRDD val locations = sc.textFile(locationFile).map(_.split(",")).map { r => // City 情報の無いデータ（subdivision_iso_code 以降が空欄）への対処 val city = if (r.length > 7) r(7) else "" City(r(0), r(4), city) } locations.registerTempTable("locations") // IPv4 のみ （::ffff: で始まるもの） を対象 val blocks = sc.textFile(blockFile).filter(_.startsWith("::ffff:")).map(_.split(",")).map { r => val mask = -1 << (128 - r(1).toInt) // (a) val startIpNum = toIpNum(r(0).replaceAll("::ffff:", "")) // (b) val endIpNum = startIpNum | ~mask IpMapping(startIpNum, endIpNum, r(2)) } blocks.registerTempTable("blocks") val ipNum = toIpNum(args(0)) val rows = sqlContext.sql(s""" select city, country from locations lo join blocks bl on bl.geonameId = lo.geonameId where startIpNum <= ${ipNum} and endIpNum >= ${ipNum} """) rows.foreach( r => println(s"${r(0)}, ${r(1)}") ) }
上記では、IP の終了値 (b) を算出するために、上位ビットを 1、下位ビットを 0 にした mask を作成し、これをビット反転して開始値 (a) と論理和をとっています。
例えば、network_start_ip が ::ffff:1.0.64.0 で network_prefix_length が 114 のデータの場合、(a) の値は 1.0.64.0 を数値化して 16793600、mask 変数の値は 2進数で ・・・111100000000000000、(b) の値は mask 変数の値をビット反転した 011111111111111 と (a) の値との論理和で 16809983 となり、16793600 ～ 16809983 の範囲内にある IP アドレスが該当する事になります。
実行 （Gradle 利用）
- Gradle 2.1
前回と同様に Gradle で実行します。
slf4j-nop を使って Spark の標準的なログ出力を抑制している点も同じです。
build.gradle
apply plugin: 'application' apply plugin: 'scala' repositories { mavenCentral() } dependencies { compile 'org.scala-lang:scala-library:2.10.4' compile('org.apache.spark:spark-sql_2.10:1.1.0') { exclude module: 'slf4j-log4j12' } runtime 'org.slf4j:slf4j-nop:1.7.7' } mainClassName = 'GetCity' run { if (project.hasProperty('args')) { args project.args.split(' ') } }
実行結果1
> gradle run -q -Pargs=1.21.127.254 Tokyo, Japan
実行結果2
> gradle run -q -Pargs=223.255.254.1 , Singapore
Spark SQL で CSV ファイルを処理 - GeoLite Legacy
以前、H2 を使って CSV ファイルを SQL で処理しましたが、今回は Spark SQL を使ってみました。
「IPアドレスから地域を特定する2 - GeoLite Legacy Country CSV」 で使った GeoLite Legacy Country CSV を使って同様の処理を Spark SQL で実装します。
今回のソースは http://github.com/fits/try_samples/tree/master/blog/20141103-2/
Spark SQL を使って IP アドレスから国判定
Spark SQL で扱うテーブルのスキーマを定義する方法はいくつか用意されているようですが、今回はケースクラスをスキーマとして登録する方法で実装しました。
処理の手順は下記のようになります。
(2) の処理で (1) のケースクラスを格納した RDD を作成し、(3) の処理で (2) で処理したオブジェクトをテーブルとして登録します。
(2) の処理までは通常の Spark の API を使った処理ですが、import sqlContext.createSchemaRDD によって (3) で registerTempTable メソッドを呼び出す際に RDD から Spark SQL の SchemaRDD へ暗黙変換が実施されます。
registerTempTable の引数としてテーブル名を渡す事で、SQL 内でこのテーブル名を使用できるようになります。
そのあとは SQL を実行して結果を出力するだけです。
foreach の要素となる org.apache.spark.sql.Row の実体は org.apache.spark.sql.catalyst.expressions.Row トレイトで、このトレイトが Seq トレイトを extends しているため head などの Seq の API も使えます。
GetCountry.scala
import org.apache.spark.SparkContext import org.apache.spark.sql.SQLContext import java.net.InetAddress // (1) スキーマ用のクラス定義 case class IpCountry(startIpNum: Long, endIpNum: Long, countryName: String) object GetCountry extends App { if (args.length < 1) { println("<ip address>") System.exit(0) } val countryFile = "GeoIPCountryWhois.csv" val sc = new SparkContext("local", "GetCountry") val sqlContext = new SQLContext(sc) // RDD を SchemaRDD へ暗黙変換するための定義 import sqlContext.createSchemaRDD // (2) CSV ファイルを処理して RDD 作成 val countries = sc.textFile(countryFile).map(_.replaceAll("\"", "").split(",")).map { d => IpCountry(d(2).toLong, d(3).toLong, d(5)) } // (3) テーブル登録 countries.registerTempTable("countries") val ipNum = Integer.toUnsignedLong( InetAddress.getByName(args(0)).hashCode ) // (4) SQL 実行 val rows = sqlContext.sql(s""" select countryName from countries where startIpNum <= ${ipNum} and endIpNum >= ${ipNum} """) rows.foreach( r => println(r.head) ) }
実行 （Gradle 利用）
- Gradle 2.1
今回は Gradle で実行するため、下記のようなビルド定義ファイルを用意しました。
現時点では、Maven のセントラルリポジトリに Scala 2.11 用の Spark SQL の JAR ファイルは用意されていないようなので、Scala 2.10.4 を使います。
今回の用途では Spark の標準的なログ出力が邪魔だったので slf4j-log4j12 の代わりに slf4j-nop を使うようにしてログ出力を抑制しました。
build.gradle
apply plugin: 'application' apply plugin: 'scala' repositories { mavenCentral() } dependencies { compile 'org.scala-lang:scala-library:2.10.4' compile('org.apache.spark:spark-sql_2.10:1.1.0') { // Spark のログ出力を抑制 exclude module: 'slf4j-log4j12' } runtime 'org.slf4j:slf4j-nop:1.7.7' } mainClassName = 'GetCountry' run { if (project.hasProperty('args')) { // コマンドライン引数の設定 args project.args.split(' ') } }
更に、Gradle のログ出力 （タスクの実行経過） も抑制したいので、-q オプションを使って実行しました。
実行結果1
> gradle run -q -Pargs=1.21.127.254 Japan
実行結果2
> gradle run -q -Pargs=223.255.254.1 Singapore
Java アプリケーションの起動スクリプト - Windows・Linux
指定ディレクトリ内の全 JAR ファイルをクラスパスへ指定して Java アプリケーションを起動するスクリプト例です。
今回のソースは http://github.com/fits/try_samples/tree/master/blog/20141103-1/
Windows の場合 （bat）
lib ディレクトリ内の全 JAR ファイルをクラスパスへ指定して Java アプリケーション （下記では sample.SampleApp） を実行する bat スクリプトは下記のようになります。
enabledelayedexpansion と !<環境変数>! で環境変数を遅延展開させる点に注意が必要です。
こうしないと CP 環境変数へ JAR のパスが正しく設定されません。
exec_java_sample.bat
@echo off setlocal enabledelayedexpansion set APP_CLASS=sample.SampleApp set BASE_DIR=%~d0%~p0 set CP=%BASE_DIR% set LIB=%BASE_DIR%lib for %%j in ("%LIB%\*.jar") do call set CP=!CP!;%%j java -cp %CP% %APP_CLASS% %* endlocal
Linux の場合 （bash）
bash の場合も bat とほぼ同じ様に書けます。
exec_java_sample.sh
#!/bin/sh APP_CLASS=sample.SampleApp BASE_DIR=`dirname $0` CP=$BASE_DIR LIB=$BASE_DIR/lib for jar in $LIB/*.jar; do CP=$CP:$jar; done java -cp $CP $APP_CLASS $*
Vagrant で VirtualBox 仮想マシンの CPU 数とメモリサイズを変更
Vagrant で VirtualBox 上の仮想マシンの CPU 数とメモリサイズを変更する方法としては、現時点で下記 2通りがあります。
- (a) cpus や memory を使用する
- (b) customize を使用する
ちなみに、Vagrant ソースの plugins/providers/virtualbox/config.rb を見ると、(a) は内部で customize を呼び出して (b) と同じ事をしているだけです。
(a) cpus や memory を使用する
cpus や memory を使って CPU 数とメモリサイズを変更する Vagrantfile は下記のようになります。
- CPU 数を 2
- メモリを 1 GB （1024 MB）
Vagrantfile 設定例 (a)
・・・ Vagrant.configure(VAGRANTFILE_API_VERSION) do |config| ・・・ config.vm.provider "virtualbox" do |v| v.cpus = 2 v.memory = 1024 end end
基本的に、こちらの方が分かり易いと思います。
(b) customize を使用する
customize を使って CPU 数とメモリサイズを変更する Vagrantfile は下記のようになります。
- CPU 数を 2
- メモリを 1 GB （1024 MB）
Vagrantfile 設定例 (b)
・・・ Vagrant.configure(VAGRANTFILE_API_VERSION) do |config| ・・・ config.vm.provider "virtualbox" do |v| v.customize ["modifyvm", :id, "--cpus", "2"] v.customize ["modifyvm", :id, "--memory", "1024"] end end
なお、上記の customize は、:id の箇所を変換した引数を VBoxManage コマンドへ渡して実行する事になります。 （Vagrant ソースの plugins/providers/virtualbox/action/customize.rb 等を参照）
つまり、仮想マシン 5f6002fc-1b3c-1523-5be3-c143831fbe05 に対して下記のようなコマンドを実行するのと同じです。
VBoxManage コマンドを使って CPU 数とメモリサイズ変更する例
> VBoxManage modifyvm 5f6002fc-1b3c-1523-5be3-c143831fbe05 --cpus 2 > VBoxManage modifyvm 5f6002fc-1b3c-1523-5be3-c143831fbe05 --memory 1024
IPアドレスから地域を特定する2 - GeoLite Legacy Country CSV
前回、GeoLite2 と GeoIP2 Java API 等のライブラリを使って IP アドレスから国と都市を特定しましたが、今回は GeoLite Legacy の Country CSV ファイル （IPv4用） を使って国を特定する処理を実装してみます。
なお、前回は IPv6 でも処理できましたが、今回は IPv4 のみを処理対象としています。
今回のソースは http://github.com/fits/try_samples/tree/master/blog/20141008/
はじめに
GeoLite Legacy の Country CSV ファイル GeoIPCountryWhois.csv のフォーマットは下記のようになっています。
Country CSV フォーマット
<開始IP>,<終了IP>,<開始IPの数値>,<終了IPの数値>,<国名コード>,<国名>
内容は下記の通りです。
GeoIPCountryWhois.csv
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia" "1.0.1.0","1.0.3.255","16777472","16778239","CN","China" "1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia" "1.0.8.0","1.0.15.255","16779264","16781311","CN","China" "1.0.16.0","1.0.31.255","16781312","16785407","JP","Japan" ・・・
3・4 列目の数値は IP アドレスを 32bit の正の整数値で表現したものです。
なお、GeoIPCountryWhois.csv ファイルは GeoLite Legacy Downloadable Databases の GeoLite Country の CSV/zip からダウンロードできます。
国の判定
GeoIPCountryWhois.csv ファイルを使った国の判定は下記のように処理できます。
注意点として、IPv4 を数値化した値は 32bit の正の整数ですが、Java に unsigned int のような型はありませんので、long 型などで扱う事になります。
また、Inet4Address の hashCode() メソッドで (1) の値を取得できるのですが、unsigned な値ではありませんので下記のような方法で変換します。
（例えば、IP アドレス 150.70.96.0 を数値化した 2521194496 は、Java の int 型では -1773772800 となります）
- (a) Java 8 から追加された
Integer.toUnsignedLong(int)メソッドを使用 - (b)
0xffffffffと AND 演算する （<Inet4Address の hashCode 値> & 0xffffffff）
get_country.groovy
if (args.length < 2) { println '<geolite country csv file> <ip address>' return } // (a) def toNumForIP = { Integer.toUnsignedLong(it.hashCode()) } // (b) 以下でも可 // def toNumForIP = { it.hashCode() & 0xffffffff } def ip = toNumForIP( InetAddress.getByName(args[1]) ) new File(args[0]).eachLine() { def r = it.replaceAll('"', '').split(',') def from = r[2] as long def to = r[3] as long if (from <= ip && ip <= to) { println r.last() System.exit(0) } } println 'Unknown'
実行結果は下記の通りです。
実行結果1
> groovy get_country.groovy GeoIPCountryWhois.csv 1.21.127.254 Japan
実行結果2
> groovy get_country.groovy GeoIPCountryWhois.csv 223.255.254.1 Singapore
実行結果3
> groovy get_country.groovy GeoIPCountryWhois.csv 192.168.1.1 Unknown
IPアドレスから地域を特定する - MaxMind DB Reader, GeoIP2 Java API
MaxMind が提供している無償の IP Geolocation DB である GeoLite と Java 用ライブラリを使って IP アドレスから国や都市を特定してみました。
今回は Java 用ライブラリの下記 2種類を試してみる事にします。
どちらも Maven のセントラルリポジトリから入手でき、MaxMind DB Reader は low-level API、GeoIP2 は high-level API な印象となっています。
また、GeoLite には下記 2種類があり、上記ライブラリで使えるのは GeoLite2 の方です。
今回のソースは http://github.com/fits/try_samples/tree/master/blog/20141004/
MaxMind DB Reader
MaxMind DB Reader を使って IP アドレスから地域を取得するコードを Groovy で実装しました。
GeoLite2 の DB ファイルを引数にして com.maxmind.db.Reader をインスタンス化し、get メソッドへ IP アドレスから作成した InetAddress を渡すだけです。
Reader の get メソッドで取得する地域情報は JSONデータ （com.fasterxml.jackson.databind.JsonNode） となります。
get_location_dbreader.groovy
@Grab('com.maxmind.db:maxmind-db:1.0.0') import com.maxmind.db.Reader if (args.length < 2) { println '<maxmind db file> <ip>' return } def reader = new Reader(new File(args[0])) println reader.get(InetAddress.getByName(args[1])) reader.close()
実行には、GeoLite2 Free Downloadable Databases から GeoLite2 City と GeoLite2 Country のどちらかの MaxMind DB をダウンロード・解凍し .mmdb ファイルを取得しておきます。
今回は GeoLite2 City の MaxMind DB GeoLite2-City.mmdb を使って、IP アドレス 1.21.127.254 の地域判定を行ってみました。 （下記の出力結果は加工しています）
実行結果1
> groovy get_location_dbreader.groovy GeoLite2-City.mmdb 1.21.127.254 { "city":{"geoname_id":1850147,"names":{・・・,"en":"Tokyo",・・・,"ja":"東京",・・・}}, "continent":{"code":"AS","geoname_id":6255147,"names":{・・・,"en":"Asia",・・・,"ja":"アジア",・・・}}, "country":{"geoname_id":1861060,"iso_code":"JP","names":{・・・,"en":"Japan",・・・"ja":"日本",・・・}}, "location":{"latitude":35.685,"longitude":139.7514,"time_zone":"Asia/Tokyo"}, "registered_country":{"geoname_id":1861060,"iso_code":"JP","names":{"de":"Japan","en":"Japan",・・・,"ja":"日本",・・・}}, "subdivisions":[{"geoname_id":1850144,"iso_code":"13","names":{・・・,"ja":"東京都"}}] }
結果は、東京だと判定されました。
なお、上記では省略してますが、言語毎の名称は en と ja だけではなく de・es・fr・pt-BR・ru・zh-CN なども設定されています。
次に、別の IP アドレス 223.255.254.1 を試してみます。
実行結果2
> groovy get_location_dbreader.groovy GeoLite2-City.mmdb 223.255.254.1 { "continent":{"code":"AS","geoname_id":6255147,"names":{・・・,"en":"Asia",・・・}}, "country":{"geoname_id":1880251,"iso_code":"SG","names":{・・・,"en":"Singapore",・・・,"ja":"シンガポール",・・・}}, "location":{"latitude":1.3667,"longitude":103.8,"time_zone":"Asia/Singapore"}, "registered_country":{"geoname_id":1880251,"iso_code":"SG","names":{・・・,"en":"Singapore",・・・,"ja":"シンガポール",・・・}} }
今度の結果には city 情報を含んでおらず、特定できなかったようです。
ちなみに、地域が全く特定できなかった場合の get メソッドの結果は null となるようです。 （プライベート IP などを使えば確認できます）
GeoIP2 Java API
次に GeoIP2 の方を使った場合の Groovy スクリプトは下記のようになります。
使用する .mmdb によって DatabaseReader の使用可能なメソッドが異なるようなので注意が必要です。
今回のように GeoLite2-City.mmdb ファイルを使う場合、city メソッドが使えますが、country メソッドは使えないようです。 （UnsupportedOperationException が発生しました）
get_location_geoip2.groovy
@Grab('com.maxmind.geoip2:geoip2:2.0.0') import com.maxmind.geoip2.DatabaseReader if (args.length < 2) { println '<maxmind db file> <ip>' return } def reader = new DatabaseReader.Builder(new File(args[0])).build() def res = reader.city(InetAddress.getByName(args[1])) println res.country println res.city reader.close()
実行結果は下記の通りです。
実行結果1
> groovy get_location_geoip2.groovy GeoLite2-City.mmdb 1.21.127.254 Japan Tokyo
実行結果2
> groovy get_location_geoip2.groovy GeoLite2-City.mmdb 223.255.254.1 Singapore
SQL から参照しているテーブルを抽出 - FoundationDB SQL Parser
FoundationDB SQL Parser を使って SQL から参照しているテーブル （from 句で使われているテーブル） を抽出する方法をご紹介します。
これを応用すれば SQL から CRUD 図を生成するような処理も比較的容易に実装できると思います。
今回使用したソースは http://github.com/fits/try_samples/tree/master/blog/20140920/
(1) SQL のパース
まずは、SQL をパースしてその結果を出力してみます。
パースは SQLParser オブジェクトの parseStatement メソッドへ SQL 文字列を渡すだけです。
また、parseStatement メソッドの戻り値である CursorNode オブジェクトの treePrint メソッドを呼び出せばパース結果を整形して出力してくれます。
sql_parse.groovy
@Grab('com.foundationdb:fdb-sql-parser:1.4.0') import com.foundationdb.sql.parser.SQLParser def parser = new SQLParser() // SQL のパース def node = parser.parseStatement(new File(args[0]).text) // パース結果の出力 node.treePrint()
実行結果は下記の通りです。
実行結果
> groovy sql_parse.groovy sample1.sql
com.foundationdb.sql.parser.CursorNode@46271dd6
name: null
updateMode: UNSPECIFIED
statementType: SELECT
resultSet:
com.foundationdb.sql.parser.SelectNode@11bb571c
isDistinct: false
resultColumns:
com.foundationdb.sql.parser.ResultColumnList@7c51f34b
・・・
fromList:
com.foundationdb.sql.parser.FromList@2a225dd7
・・・
leftResultSet:
com.foundationdb.sql.parser.FromBaseTable@125290e5
tableName: orders
・・・
correlation Name: od
null
rightResultSet:
com.foundationdb.sql.parser.FromBaseTable@6fa34d52
tableName: order_items
・・・
correlation Name: oi
null
joinClause:
com.foundationdb.sql.parser.BinaryRelationalOperatorNode@57576994
operator: =
・・・
[1]:
com.foundationdb.sql.parser.FromBaseTable@1205bd62
tableName: users
・・・
correlation Name: us
us
whereClause:
com.foundationdb.sql.parser.AndNode@7ef27d7f
operator: and
methodName: and
type: null
leftOperand:
com.foundationdb.sql.parser.BinaryRelationalOperatorNode@490caf5f
operator: >
・・・
rightOperand:
com.foundationdb.sql.parser.BinaryRelationalOperatorNode@31920ade
operator: =
・・・
上記で使用した SQL は下記の通り。
sample1.sql
select * from orders od join order_items oi on od.order_id = oi.order_id , users us where od.ordered_date > ? and od.user_id = us.user_id
(2) テーブルの抽出
それでは、SQL 内で参照しているテーブルを抽出してみます。
テーブルの抽出には com.foundationdb.sql.parser.Visitor インターフェースを使うのが簡単です。 （いわゆる Visitor パターン）
具体的には、下記処理を実装した Visitor インターフェースの実装オブジェクトを用意し、パース結果 （CursorNode） の accept メソッドへ渡します。
skipChildren メソッド等の Visitor インターフェースのその他メソッドは、全ノードを処理するように false を返すようにしておきます。
テーブル名 （スキーマ名付き） を文字列で取得するには FromBaseTable オブジェクトの getOrigTableName メソッドで取得した TableName オブジェクトの getFullTableName メソッドを使います。
ここで、テーブル名取得系のメソッドにはいくつかバリエーションがありますが、主なものは下記の通りです。
| クラス | メソッド | 内容 | 戻り値の型 |
|---|---|---|---|
| FromBaseTable | getOrigTableName | テーブル名 | TableName |
| FromBaseTable | getTableName | テーブルの別名 （別名が無ければテーブル名） | TableName |
| FromBaseTable | getCorrelationName | テーブルの別名 （別名が無ければ null） | String |
| TableName | getFullTableName | スキーマ名付きテーブル名 | String |
| TableName | getTableName | スキーマ名なしテーブル名 | String |
また、下記サンプルでは、テーブル名の重複を排除しテーブル名でソートするように TreeSet へテーブル名を登録するようにしています。
listup_tables.groovy
@Grab('com.foundationdb:fdb-sql-parser:1.4.0') import com.foundationdb.sql.parser.FromBaseTable import com.foundationdb.sql.parser.SQLParser import com.foundationdb.sql.parser.Visitor def tables = [] as TreeSet // FromBaseTable （参照しているテーブル）を処理するための Visitor 実装 def tableSelector = [ visit: { node -> if (node instanceof FromBaseTable) { // テーブル名（スキーマ名付き）を取得して tables へ追加 tables << node.origTableName.fullTableName } node }, skipChildren: { node -> false }, stopTraversal: { -> false }, visitChildrenFirst: { node -> false} ] as Visitor def parser = new SQLParser() def node = parser.parseStatement(new File(args[0]).text) // テーブル名の抽出 node.accept(tableSelector) // テーブル名の出力 tables.each { println it }
「(1) SQL のパース」 で使用した sample1.sql からテーブル名を抽出すると下記のような結果になりました。
実行結果1
> groovy listup_tables.groovy sample1.sql order_items orders users
更に、下記のような SQL からテーブル名を抽出してみます。
sample2.sql
select us.user_id, us.name, (select count(*) from REFUNDS rf where rf.user_id = us.user_id) as refund_count1, (select count(*) from SCH1.REFUNDS rf where rf.user_id = us.user_id) as refund_count2 from Users us where us.user_id in ( select ou.user_id from ( select od.user_id, count(*) ct from orders od join order_items oi on od.item_id = oi.item_id where oi.item_id in ( select item_id from special_items ) and od.ordered_date > ? group by od.user_id ) ou where ou.ct >= 5 )
結果は下記の通り、テーブル名は全て小文字になりましたが正常に抽出されています。
getFullTableName でテーブル名を取得しているので、スキーマ名を付けたテーブルと付けなかったテーブルは別途出力されています。
実行結果2
> groovy listup_tables.groovy sample2.sql order_items orders refunds sch1.refunds special_items users
