3 Big Data Spatial and GraphとOracle Databaseとの統合

Oracle Big Data Connectorsを使用して、Big Data Spatial and GraphとOracle Databaseとの間の空間データ・アクセスを簡略化できます。

この章では、次に関する知識があることを前提としています。

• Oracle SQL Connector for HDFS

  詳細は、「Oracle SQL Connector for Hadoop Distributed File System」を参照してください。

• Oracle Loader for Hadoop

  詳細は、「Oracle Loader for Hadoop」を参照してください

• Apache Hive

  詳細は、Apache Hiveのドキュメント(https://cwiki.apache.org/confluence/display/Hive/Home#Home-UserDocumentation)を参照してください。

• 区切りテキスト・ファイルを使用したOracle SQL Connector for HDFSの使用
  このトピックは、HDFS内のファイルが区切りテキスト・ファイルである(フィールドはカンマやタブなどの単一文字のマーカーを使用して区切る必要があります)と同時に、空間データがGeoJSONまたはWKT形式で格納されている場合に適用されます。
• Hive表を使用したOracle SQL Connector for HDFSの使用
  Oracle SQL Connector for HDFS (OSCH)は、HDFSで定義されたHIVE表を直接サポートします。
• Oracle Loader for Hadoopによって生成されたファイルを使用したOracle SQL Connector for HDFSの使用
  Oracle Loader for Hadoop (OLH)によって生成されたファイルを使用してOracle SQL Connector for HDFS (OSCH)を使用するには、OLHを使用してHDFSからOracle Databaseにデータを移動する方法を理解する必要があります。
• Oracle Big Data SQLを使用したOracle DatabaseとのHDFS空間データの統合
  Oracle Big Data SQLを使用して、HDFSとOracle Databaseとの間の空間データのアクセスを容易にすることができます。

3.1 区切りテキスト・ファイルを使用したOracle SQL Connector for HDFSの使用

このトピックは、HDFS内のファイルが区切りテキスト・ファイルである(フィールドはカンマやタブなどの単一文字のマーカーを使用して区切る必要があります)と同時に、空間データがGeoJSONまたはWKT形式で格納されている場合に適用されます。

このようなデータがBig Data Spatial and Graphによって使用され、Oracle SQL Connector for HDFSを使用してOracleデータベースからアクセスされる場合、特定の構成ステップが必要です。

この例では、次のように、改行によって区切られたレコードがHDFS内のファイルに含まれ、各レコード内のフィールドがタブによって区切られていることを前提としています。

"6703"	1	62	"Hong Kong"	3479846	POINT (114.18306 22.30693)
"6702"	57	166	"Singapore"	1765655	POINT (103.85387 1.29498) 

1. Hadoopクラスタのノードにログインします。

2. 次の例のように、OSCH (Oracle SQL Connector for HDFS)に必要な構成ファイルを作成します。

   <?xml version="1.0"?>
    <configuration>
       <property>
         <name>oracle.hadoop.exttab.tableName</name>
         <value>TWEETS_EXT_TAB_FILE</value> 
       </property> 
      <property>
         <name>oracle.hadoop.exttab.sourceType</name>
         <value>text</value>
       </property>
      <property> 
         <name>oracle.hadoop.exttab.dataPaths</name>
         <value>/user/scott/simple_tweets_data/*.log</value> 
       </property>   
       <property>
         <name>oracle.hadoop.connection.url</name>
         <value>jdbc:oracle:thin:@//myhost:1521/myservicename</value> 
       </property> 
       <property>
         <name>oracle.hadoop.connection.user</name>
         <value>scott</value> 
       </property>      
       <property>
         <name>oracle.hadoop.exttab.fieldTerminator</name>
         <value>\u0009</value> 
       </property>      
       <property>
         <name>oracle.hadoop.exttab.columnNames</name>
         <value>ID,FOLLOWERS_COUNT,FRIENDS_COUNT,LOCATION,USER_ID,GEOMETRY</value> 
       </property>      
       <property>
         <name>oracle.hadoop.exttab.defaultDirectory</name>
         <value>TWEETS_DT_DIR</value> 
       </property>      
   </configuration>
   

3. 構成ファイルにtweets_text.xmlという名前を付けます。

4. Hadoopクラスタのノード上で、次のコマンドを実行します。

   hadoop jar $OSCH_HOME/jlib/orahdfs.jar \
          oracle.hadoop.exttab.ExternalTable \
          -conf /home/oracle/tweets_text.xml \
          -createTable
   

   コマンドを実行すると、データベース・パスワードを要求されます。

   OSCH_HOME環境変数を作成することも、コマンド構文のOSCH_HOMEをOracle SQL Connector for HDFSのインストール・ディレクトリのフル・パスに置き換えることもできます。Oracle Big Data Applianceの場合、このディレクトリは/opt/oracle/orahdfs-versionです

これで、表TWEETS_EXT_TAB_FILEに対して問い合せることが可能になります。データベースから他の任意の表と同じように問い合せることができます。このデータベースは、前述のステップで構成ファイルに指定されているターゲット・データベースです。次の問合せでは、表内の行の数を選択します。

select count(*) from TWEETS_EXT_TAB_FILE;

データベース内の他の任意の空間表の場合と同じように、この表に対して空間操作を実行できます。次の例では、特定の映画館から半径4分の1マイル(0.25マイル)内でツイートしているユーザーに関する情報を取得します。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 0.05, 'UNIT=MILE'), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_FILE tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

ここでは、表CINEMAはOracleデータベース内の空間表であり、HDFS表TWEETS_EXT_TAB_FILEを使用してこの表に対して問い合せることができます。ツイート表からのデータはWKT (Well-Known Text)として読み込まれ、SDO_GEOMETRYのWKTコンストラクタを使用してこのデータがデータベース内のジオメトリとしてマテリアライズされます。

ジオメトリのSRIDは8307です。また、空間データがGeoJSON形式である場合、問合せは次のようにする必要があります。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 0.05, 'UNIT=MILE'), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_FILE tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

3.2 Hive表を使用したOracle SQL Connector for HDFSの使用

Oracle SQL Connector for HDFS (OSCH)は、HDFSで定義されたHIVE表を直接サポートします。

Hive表は、パーティション化せずに、ROW FORMAT DELIMITED and FILE FORMAT TEXTFILE句を使用して定義する必要があります。空間データは、GeoJSONまたはWKT形式である必要があります。

Hive管理表とHive外部表はどちらもサポートされます。

たとえば、「区切りテキスト・ファイルを使用したOracle SQL Connector for HDFSの使用」で説明されているファイル上で表を作成するためのHiveコマンドは、次のとおりです。ここでは、HDFSデータに定義されたHive表をユーザーがすでに所有していることが前提です。HDFS内のデータがサポート対象の形式である必要があり、空間データがGeoJSONまたはWKT形式である必要があります。

CREATE EXTERNAL TABLE IF NOT EXISTS TWEETS_HIVE_TAB(
  ID string, 
  FOLLOWERS_COUNT int, 
  FRIENDS_COUNT int, 
  LOCATION string, 
  USER_ID int, 
  GEOMETRY string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '/user/scott/simple_tweets_data';

次の例では、表に対して問い合せます。

select ID, FOLLOWERS_COUNT, FRIENDS_COUNT, LOCATION, USER_ID, GEOMETRY from TWEETS_HIVE_TAB limit 10;

出力内容は次のようになります。

"6703"	1	62	"Hong Kong"	3479846	POINT (114.18306 22.30693)
"6702"	57	166	"Singapore"	1765655	POINT (103.85387 1.29498)

1. Hadoopクラスタのノードにログインします。

2. 次の例のように、OSCH (Oracle SQL Connector for HDFS)に必要な構成ファイルを作成します。

   <?xml version="1.0"?>
    <configuration>
       <property>
         <name>oracle.hadoop.exttab.tableName</name>
         <value>TWEETS_EXT_TAB_HIVE</value> 
       </property> 
      <property> 
         <name>oracle.hadoop.exttab.sourceType</name>
         <value>hive</value> 
       </property>   
      <property> 
         <name>oracle.hadoop.exttab.hive.tableName</name>
         <value>TWEETS_HIVE_TAB</value> 
       </property>   
      <property> 
         <name>oracle.hadoop.exttab.hive.databaseName</name>
         <value>default</value> 
       </property>   
       <property>
         <name>oracle.hadoop.connection.url</name>
         <value>jdbc:oracle:thin:@//myhost:1521/myservicename</value> 
       </property> 
       <property>
         <name>oracle.hadoop.connection.user</name>
         <value>scott</value> 
       </property>      
       <property>
         <name>oracle.hadoop.exttab.defaultDirectory</name>
         <value>TWEETS_DT_DIR</value> 
       </property>      
   </configuration> 
   

3. 構成ファイルにtweets_text.xmlという名前を付けます。

4. Hadoopクラスタのノード上で、次のコマンドを実行します。

   # Add HIVE_HOME/lib* to HADOOP_CLASSPATH.  
   export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
   hadoop jar $OSCH_HOME/jlib/orahdfs.jar \
          oracle.hadoop.exttab.ExternalTable \
          -conf /home/oracle/tweets_hive.xml \
          -createTable
   

   コマンドを実行すると、データベース・パスワードを要求されます。OSCH_HOME環境変数を作成することも、コマンド構文のOSCH_HOMEをOracle SQL Connector for HDFSのインストール・ディレクトリのフル・パスに置き換えることもできます。Oracle Big Data Applianceの場合、このディレクトリは/opt/oracle/orahdfs-versionです

   Hiveインストール・ディレクトリを指し示すように環境変数HIVE_HOMEを設定します(/usr/lib/hiveなど)。

これで、表TWEETS_EXT_TAB_FILEに対して問い合せることが可能になります。データベースから他の任意の表と同じように問い合せることができます。次の問合せでは、表内の行の数を選択します。

select count(*) from TWEETS_EXT_TAB_HIVE;;

データベース内の他の任意の空間表の場合と同じように、この表に対して空間操作を実行できます。次の例では、特定の映画館から半径4分の1マイル(0.25マイル)内でツイートしているユーザーに関する情報を取得します。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 0.05, 'UNIT=MILE), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_HIVE tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

ここでは、表CINEMAはOracleデータベース内の空間表であり、HDFS表TWEETS_EXT_TAB_FILEを使用してこの表に対して問い合せることができます。ツイート表からのデータはWKT (Well-Known Text)として読み込まれ、SDO_GEOMETRYのWKTコンストラクタを使用してこのデータがデータベース内のジオメトリとしてマテリアライズされます。

ジオメトリのSRIDは8307です。また、空間データがGeoJSON形式である場合、問合せは次のようにする必要があります。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 0.05, 'UNIT=MILE), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_HIVE tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 'DISTANCE=0.25 UNIT=MILE') = 'TRUE'

3.3 Oracle Loader for Hadoopによって生成されたファイルを使用したOracle SQL Connector for HDFSの使用

Oracle Loader for Hadoop (OLH)によって生成されたファイルを使用してOracle SQL Connector for HDFS (OSCH)を使用するには、OLHを使用してHDFSからOracle Databaseにデータを移動する方法を理解する必要があります。

Big Data Spatial and Graph空間データをデータベースに移動するには、変更が必要です。通常、このソリューションは、HDFS内の任意の種類のファイルまたは任意の種類のHiveデータに適用されます。空間情報は、ウェル・ノウン形式またはカスタム形式にすることができます。

最初は、空間情報がユーザー定義の形式で含まれるHDFS内のファイルから外部表を作成する方法の例です。HDFS内のファイルには次の形式のレコードが含まれることを前提とします。

{
	"type":"Feature",
	"id":"6703",
	"followers_count":1,
	"friends_count":62,
	"location":"Hong Kong",
	"user_id":3479846,
	"longitude":114.18306,
	"latitude":22.30693
}

{
	"type":"Feature",
	"id":"6702",
	"followers_count":57,
	"friends_count":166,
	"location":"Singapore",
	"user_id":1765655,
	"longitude":103.85387,
	"latitude":1.29498
}

これらのレコード用の表を作成するためのHiveコマンドは、次のとおりです。

add jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/ojdbc8.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdoutl.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdoapi.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdohadoop-vector.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdohadoop-vector-hive.jar
     … (add here jars containing custom SerDe and/or InputFormats);
CREATE EXTERNAL TABLE IF NOT EXISTS CUST_TWEETS_HIVE_TAB (id STRING, geometry STRING, followers_count STRING, friends_count STRING, location STRING, user_id STRING)                                         
ROW FORMAT SERDE 'mypackage.TweetsSerDe'              
STORED AS INPUTFORMAT 'oracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/user/scott/simple_tweets_data';

InputFormatオブジェクトoracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormatは、これらのレコードが厳密なGeoJSONでない場合でも、これらを読み込むことができます。このため、前述の例では、カスタムInputFormat仕様は必要ありません。ただし、経度および緯度をWKTまたはGeoJSONジオメトリに変換するためのカスタムHive Serializer and Deserializer (SerDe)は必要です。そのためには、次の例のように、SerDeのデシリアライズ関数でSpatial Java APIを使用できます

    @Override
    public Object deserialize(Writable w) throws SerDeException {
        Text rowText = (Text) w;
        List<Text> row = new ArrayList<Text>(columnNames.size());
        
        //default all values to null
        for(int i=0;i<columnNames.size();i++){
        	row.add(null);
        }
        
        // Try parsing row into JSON object
        JsonNode recordNode = null;
        
        try {
        	String txt = rowText.toString().trim();
        	recordNode = jsonMapper.readTree(txt);
			row.set(columnNames.indexOf("id"), new Text(recordNode.get("id").getTextValue()));
			row.set(columnNames.indexOf("followers_count"), new Text(recordNode.get("followers_count").toString()));
			row.set(columnNames.indexOf("friends_count"), new Text(recordNode.get("friends_count").toString()));
			row.set(columnNames.indexOf("location"), new Text(recordNode.get("location").getTextValue()));
			row.set(columnNames.indexOf("user_id"), new Text(recordNode.get("user_id").toString()));
			
			Double longitude = recordNode.get("longitude").getDoubleValue();
			Double latitude = recordNode.get("latitude").getDoubleValue();
			
			//use the Spatial API to create the geometry
			JGeometry geom = JGeometry.createPoint(new double[]{
					longitude, 
					latitude}, 
					2, //dimensions
					8307 //SRID
					);
			//Transform the JGeometry to WKT
 			String geoWKT = new String(wkt.fromJGeometry(geom));
			row.set(columnNames.indexOf("geometry"), new Text(geoWKT));
        } catch (Exception e) {
            throw new SerDeException("Exception parsing JSON: " +e.getMessage(), e);
        }
	
        return row;
    }    

前述の例では、ジオメトリをGeoJSON形式で返すために、置換元として次の部分を、

String geoWKT = new String(wkt.fromJGeometry(geom));
row.set(columnNames.indexOf("geometry"), new Text(geoWKT));

次のようにします。

row.set(columnNames.indexOf("geometry"), new Text(geom.toGeoJson()));

Spatial Java APIを使用してGeoJSON、WKTまたはESRI Shapefile内のデータを変換する別のSerDeの例は、フォルダ/opt/oracle/oracle-spatial-graph/spatial/vector/examples/hive/java/src/oracle/spatial/hadoop/vector/hive/java/src/serde内にあります

次の例では、Hive表に対して問い合せます。

select ID, FOLLOWERS_COUNT, FRIENDS_COUNT, LOCATION, USER_ID, GEOMETRY from CUST_TWEETS_HIVE_TAB limit 10;

出力は次のようになります。

6703	1	62	Hong Kong	3479846	POINT (114.18306 22.30693)
6702	57	166	Singapore	1765655	POINT (103.85387 1.29498)

• HDFSデータ・ポンプ・ファイルまたは区切りテキスト・ファイルの作成
  
• SQL Connector for HDFSの作成
  

3.3.1 HDFSデータ・ポンプ・ファイルまたは区切りテキスト・ファイルの作成

「Oracle Loader for Hadoopによって生成されたファイルを使用したOracle SQL Connector for HDFSの使用」のHive表を使用して、HDFSデータ・ポンプ・ファイルまたは区切りテキスト・ファイルを作成できます。

1. 次のように、Oracle Databaseに表を作成します。

   CREATE TABLE tweets_t(id INTEGER
     PRIMARY KEY, geometry VARCHAR2(4000), followers_count NUMBER,
     friends_count NUMBER, location VARCHAR2(4000), user_id NUMBER);
   

   この表は、ターゲット表として使用されます。Oracle Loader for HadoopはOracleデータベースの表のメタデータを使用して列名、データ型、パーティションおよび他の情報を識別します。簡単にするために、Hive表と同じ列(フィールド)を使用してこの表を作成します。外部表が作成された後、この表を削除することも、この表を使用して外部表からターゲット表に行を挿入することもできます。(ターゲット表の詳細は、「ターゲット表のメタデータについて」を参照してください。

2. 次の例のように、ローダー構成ファイルを作成します。

   <?xml version="1.0" encoding="UTF-8" ?>
   <configuration>
   <!--                          Input settings                             -->
   <property>
   	<name>mapreduce.inputformat.class</name>
   	<value>oracle.hadoop.loader.lib.input.HiveToAvroInputFormat</value>
   </property>
   <property>
   	<name>oracle.hadoop.loader.input.hive.databaseName</name>
   	<value>default</value>
   </property>
   <property>
   	<name>oracle.hadoop.loader.input.hive.tableName</name>
   	<value>CUST_TWEETS_HIVE_TAB</value>
   </property>
   <!--                          Output settings                             -->
    <property>
      <name>mapreduce.outputformat.class</name>
      <value>oracle.hadoop.loader.lib.output.DataPumpOutputFormat</value>
    </property>
    <property>
      <name>mapred.output.dir</name>
      <value>/user/scott/data_output</value>
    </property>
   <!--                          Table information                            -->
   <property>
   	<name>oracle.hadoop.loader.loaderMap.targetTable</name>
   	<value>tweets_t</value>
   </property> 
   <!--                          Connection information                      -->
   <property>
     <name>oracle.hadoop.loader.connection.url</name>
     <value>jdbc:oracle:thin:@//myhost:1521/myservicename</value>
   </property>
   <property>
       <name>oracle.hadoop.loader.connection.user</name>
       <value>scott</value>
   </property>
   <property>
       <name>oracle.hadoop.loader.connection.password</name>
       <value>welcome1</value>        
       <description> Having the password in cleartext is NOT RECOMMENDED. Use Oracle Wallet instead. </description>
   </property>
   </configuration>
   

   この構成により、データ・ポンプ・ファイルがHDFSに作成されます。区切りテキスト・ファイルを出力として使用するには、置換元として次の部分を、

   oracle.hadoop.loader.lib.output.DataPumpOutputFormat

   次のようにします。

   oracle.hadoop.loader.lib.output.DelimitedTextOutputFormat

3. 構成ファイルにtweets_hive_to_data_pump.xmlという名前を付けます。

4. データ・ポンプ・ファイルを作成します。

   # Add HIVE_HOME/lib* and the Hive configuration directory to HADOOP_CLASSPATH.
   export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*:$HIVE_CONF_DIR
   # Add Oracle Spatial libraries to HADOOP_CLASSPATH.
   export ORACLE_SPATIAL_VECTOR_LIB_PATH=/opt/oracle/oracle-spatial-graph/spatial/vector/jlib
   
   export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$ORACLE_SPATIAL_VECTOR_LIB_PATH/ojdbc8.jar:$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoutl.jar:$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoapi.jar:$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector.jar:$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector-hive.jar
   
   # The Oracle Spatial libraries need to be added to the libjars option as well.
   export LIBJARS=$ORACLE_SPATIAL_VECTOR_LIB_PATH/ojdbc8.jar,$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoutl.jar,$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoapi.jar,$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector.jar,$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector-hive.jar
   
   # And the following HIVE jar files have to be added to the libjars option.
   export LIBJARS=$LIBJARS,$HIVE_HOME/lib/hive-exec-*.jar,$HIVE_HOME/lib/hive-metastore-*.jar,$HIVE_HOME/lib/libfb303*.jar
   
   hadoop jar ${OLH_HOME}/jlib/oraloader.jar \
              oracle.hadoop.loader.OraLoader \
              -conf /home/oracle/tweets_hive_to_data_pump.xml \
              -libjars $LIBJARS
   

前述の例の場合:

• 環境変数OLH_HOMEをインストール・ディレクトリに設定する必要があることを確認してください。

• Hiveインストール・ディレクトリを指し示すように環境変数HIVE_HOMEを設定します(/usr/lib/hiveなど)。

• Hive構成ディレクトリを指し示すように環境変数HIVE_CONF_DIRを設定します(/etc/hive/confなど)。

• 次のHive JARファイルを、カンマ区切りリスト形式で、hadoopコマンドの-libjarsオプションに追加します。アスタリスク(*)を完全なファイル名に置き換えます。

  hive-exec-*.jar
  hive-metastore-*.jar
  libfb303*.jar
  

• oracle.kv.hadoop.hive.table.TableStorageHandlerを使用してHive表を(Oracle NoSQL Databaseから取得するデータを使用して)作成する場合、JARファイル$KVHOME/lib/kvclient.jar (KVHOMEは、Oracle NoSQL Databaseがインストールされているディレクトリです)をhadoopコマンドの-libjarsオプションに追加することも必要です。

• org.apache.hadoop.hive.hbase.HBaseStorageHandlerを使用してHive表を(Apache HBaseから取得するデータを使用して)作成する場合、次のJARファイルを、カンマ区切りリスト形式で、hadoopコマンドの-libjarsオプションに追加することも必要です。

  $HIVE_HOME/lib/hbase-server.jar
  $HIVE_HOME/lib/hive-hbase-handler.jar
  $HIVE_HOME/lib/hbase-common.jar
  $HIVE_HOME/lib/hbase-client.jar
  $HIVE_HOME/lib/hbase-hadoop2-compat.jar
  $HIVE_HOME/lib/hbase-hadoop-compat.jar
  $HIVE_HOME/lib/hbase-protocol.jar
  $HIVE_HOME/lib/htrace-core.jar
  

3.3.2 SQL Connector for HDFSの作成

SQL Connector fo HDFSを作成するには、このトピックの手順を実行します。

1. 次の例のように、SQL Connector for HDFSの構成ファイルを作成します。

   <?xml version="1.0"?>
    <configuration>
       <property>
         <name>oracle.hadoop.exttab.tableName</name>
         <value>TWEETS_EXT_TAB_DP</value> 
       </property> 
       <property>
         <name>oracle.hadoop.exttab.sourceType</name>
         <value>datapump</value> 
       </property> 
      <property> 
         <name>oracle.hadoop.exttab.dataPaths</name>
         <value>/user/scott/data_output/oraloader-0000*.dat</value>
       </property>   
       <property>
         <name>oracle.hadoop.connection.url</name>
         <value>jdbc:oracle:thin:@//myhost:1521/myservicename</value> 
       </property> 
       <property>
         <name>oracle.hadoop.connection.user</name>
         <value>scott</value> 
       </property>      
       <property>
         <name>oracle.hadoop.exttab.defaultDirectory</name>
         <value>TWEETS_DT_DIR</value> 
       </property>   
   </configuration>
   

   ファイルが区切りテキスト・ファイルである場合、「区切りテキスト・ファイルを使用したOracle SQL Connector for HDFSの使用」のステップを実行します。

2. 構成ファイルにtweets_ext_from_dp.xmlという名前を付けます。

3. 外部表を作成します。

   hadoop jar $OSCH_HOME/jlib/orahdfs.jar \
              oracle.hadoop.exttab.ExternalTable \
              -conf /home/oracle/tweets_ext_from_dp.xml\
              -createTable
   

   前述のコマンドでは、OSCH_HOME環境変数を作成することも、コマンドのOSCH_HOMEをOracle SQL Connector for HDFSのインストール・ディレクトリのフル・パスに置き換えることもできます。Oracle Big Data Applianceの場合、このディレクトリは/opt/oracle/orahdfs-versionです

これで、表TWEETS_EXT_TAB_DPに対して問い合せることが可能になります。データベース内の他の任意の表と同じように問い合せることができます。次に例を示します。

select count(*) from TWEETS_EXT_TAB_DP;

この表に対して次の例のような空間操作を実行し、映画館の半径4分の1マイル以内でツイートしているユーザーを取得できます。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_DP tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';

この情報をさらに使用して広告カスタマイズすることもできます。

ジオメトリのSRIDは8307です。また、空間データがGeoJSON形式である場合、問合せは次のようにする必要があります。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, TWEETS_EXT_TAB_DP tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';

3.4 Oracle Big Data SQLを使用したOracle DatabaseとのHDFS空間データの統合

Oracle Big Data SQLを使用して、HDFSとOracle Databaseとの間の空間データ・アクセスを簡略化できます。

Oracle Big Data SQL内の空間機能を有効にするには、ファイルbigdata.propertiesを更新し、末尾に次の行を追加します($ORACLE_SPATIAL_VECTOR_LIB_PATHはOracle Spatialライブラリへのパスに置き換えます)。

java.classpath.user=$ORACLE_SPATIAL_VECTOR_LIB_PATH/ojdbc8.jar:
$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoutl.jar: $ORACLE_SPATIAL_VECTOR_LIB_PATH/sdoapi.jar:
$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector.jar:
$ORACLE_SPATIAL_VECTOR_LIB_PATH/sdohadoop-vector-hive.jar
(Also add here jars containing custom SerDe and/or InputFormat specifications.)

ファイルがHDFS内にある場合、次のソリューションを使用できます。

• Big Data SQLを使用したHDFSファイル用のOracle外部表の作成

• Big Data SQLを使用したHive表の使用によるOracle外部表の作成

Oracle NoSQL DatabaseまたはApache HBaseから空間データにアクセスする場合、「Big Data SQLを使用したHive表の使用によるOracle外部表の作成」のソリューションを使用できます。

Oracle Loader for Hadoop (OLH)によって生成されたファイルを使用してOracle SQL Connector for HDFS (OSCH)を使用するには、OLHを使用してHDFSからOracle Databaseにデータを移動する方法を理解する必要があります。

Big Data Spatial and Graph空間データをデータベースに移動するには、変更が必要です。通常、このソリューションは、HDFS内の任意の種類のファイルまたは任意の種類のHiveデータに適用されます。空間情報は、ウェル・ノウン形式またはカスタム形式にすることができます。

最初は、空間情報がユーザー定義の形式で含まれるHDFS内のファイルから外部表を作成する方法の例です。HDFS内のファイルには次の形式のレコードが含まれることを前提とします。

{
	"type":"Feature",
	"id":"6703",
	"followers_count":1,
	"friends_count":62,
	"location":"Hong Kong",
	"user_id":3479846,
	"longitude":114.18306,
	"latitude":22.30693
}

{
	"type":"Feature",
	"id":"6702",
	"followers_count":57,
	"friends_count":166,
	"location":"Singapore",
	"user_id":1765655,
	"longitude":103.85387,
	"latitude":1.29498
}

これらのレコード用の表を作成するためのHiveコマンドは、次のとおりです。

add jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/ojdbc8.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdoutl.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdoapi.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdohadoop-vector.jar
     /opt/oracle/oracle-spatial-graph/spatial/vector/jlib/sdohadoop-vector-hive.jar
     … (add here jars containing custom SerDe and/or InputFormats);
CREATE EXTERNAL TABLE IF NOT EXISTS CUST_TWEETS_HIVE_TAB (id STRING, geometry STRING, followers_count STRING, friends_count STRING, location STRING, user_id STRING)                                         
ROW FORMAT SERDE 'mypackage.TweetsSerDe'              
STORED AS INPUTFORMAT 'oracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/user/scott/simple_tweets_data';

InputFormatオブジェクトoracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormatは、これらのレコードが厳密なGeoJSONでない場合でも、これらを読み込むことができます。このため、前述の例では、カスタムInputFormat仕様は必要ありません。ただし、経度および緯度をWKTまたはGeoJSONジオメトリに変換するためのカスタムHive Serializer and Deserializer (SerDe)は必要です。そのためには、次の例のように、SerDeのデシリアライズ関数でSpatial Java APIを使用できます

    @Override
    public Object deserialize(Writable w) throws SerDeException {
        Text rowText = (Text) w;
        List<Text> row = new ArrayList<Text>(columnNames.size());
        
        //default all values to null
        for(int i=0;i<columnNames.size();i++){
        	row.add(null);
        }
        
        // Try parsing row into JSON object
        JsonNode recordNode = null;
        
        try {
        	String txt = rowText.toString().trim();
        	recordNode = jsonMapper.readTree(txt);
			row.set(columnNames.indexOf("id"), new Text(recordNode.get("id").getTextValue()));
			row.set(columnNames.indexOf("followers_count"), new Text(recordNode.get("followers_count").toString()));
			row.set(columnNames.indexOf("friends_count"), new Text(recordNode.get("friends_count").toString()));
			row.set(columnNames.indexOf("location"), new Text(recordNode.get("location").getTextValue()));
			row.set(columnNames.indexOf("user_id"), new Text(recordNode.get("user_id").toString()));
			
			Double longitude = recordNode.get("longitude").getDoubleValue();
			Double latitude = recordNode.get("latitude").getDoubleValue();
			
			//use the Spatial API to create the geometry
			JGeometry geom = JGeometry.createPoint(new double[]{
					longitude, 
					latitude}, 
					2, //dimensions
					8307 //SRID
					);
			//Transform the JGeometry to WKT
 			String geoWKT = new String(wkt.fromJGeometry(geom));
			row.set(columnNames.indexOf("geometry"), new Text(geoWKT));
        } catch (Exception e) {
            throw new SerDeException("Exception parsing JSON: " +e.getMessage(), e);
        }
	
        return row;
    }    

前述の例では、ジオメトリをGeoJSON形式で返すために、置換元として次の部分を、

String geoWKT = new String(wkt.fromJGeometry(geom));
row.set(columnNames.indexOf("geometry"), new Text(geoWKT));

次のようにします。

row.set(columnNames.indexOf("geometry"), new Text(geom.toGeoJson()));

Spatial Java APIを使用してGeoJSON、WKTまたはESRI Shapefile内のデータを変換する別のSerDeの例は、フォルダ/opt/oracle/oracle-spatial-graph/spatial/vector/examples/hive/java/src/oracle/spatial/hadoop/vector/hive/java/src/serde内にあります

次の例では、Hive表に対して問い合せます。

select ID, FOLLOWERS_COUNT, FRIENDS_COUNT, LOCATION, USER_ID, GEOMETRY from CUST_TWEETS_HIVE_TAB limit 10;

出力は次のようになります。

6703	1	62	Hong Kong	3479846	POINT (114.18306 22.30693)
6702	57	166	Singapore	1765655	POINT (103.85387 1.29498)

• Big Data SQLを使用したHDFSファイル用のOracle外部表の作成
  
• Big Data SQLを使用したHive表の使用によるOracle外部表の作成
  

3.4.1 Big Data SQLを使用したHDFSファイル用のOracle外部表の作成

HDFS内の任意の種類のファイル用としてOracle外部表を作成できます。空間情報は、ウェル・ノウン形式またはカスタム形式にすることができます。

ジオメトリ形式がWKTまたはGeoJSONでない場合、フォルダ/opt/oracle/oracle-spatial-graph/spatial/vector/examples/hive/java/src/oracle/spatial/hadoop/vector/hive/java/src/serde内に用意されているSerDeの例の1つを使用するか、「Oracle Loader for Hadoopによって生成されたファイルを使用したOracle SQL Connector for HDFSの使用」の例のようにカスタムSerDeを作成します。

その後、次の例のように、Oracle外部表を作成します。

CREATE TABLE SAMPLE_TWEETS (id VARCHAR2(4000), 
  geometry VARCHAR2(4000), 
  followers_count VARCHAR2(4000), 
  friends_count VARCHAR2(4000), 
  location VARCHAR2(4000), user_id VARCHAR2(4000)) ORGANIZATION EXTERNAL
             (TYPE oracle_hdfs DEFAULT DIRECTORY DEFAULT_DIR
 ACCESS PARAMETERS (
  com.oracle.bigdata.rowformat: \
     SERDE 'mypackage.TweetsSerDe'
  com.oracle.bigdata.fileformat: \
     INPUTFORMAT 'oracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormat' \
     OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' \
  )
LOCATION ('/user/scott/simple_tweets_data/*.log'));

これで、表SAMPLE_TWEETSに対して問い合せることが可能になります。データベース内の他の任意の表と同じように問い合せることができます。次に例を示します。

select count(*) from SAMPLE_TWEETS;

この表に対して次の例のような空間操作を実行し、映画館の半径4分の1マイル以内でツイートしているユーザーを取得できます。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, SAMPLE_TWEETS tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';

この情報をさらに使用して広告カスタマイズすることもできます。

ジオメトリのSRIDは8307です。また、空間データがGeoJSON形式である場合、問合せは次のようにする必要があります。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, SAMPLE_TWEETS tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';

3.4.2 Big Data SQLを使用したHive表の使用によるOracle外部表の作成

Big Data SQLを使用してHive表を使用してOracle外部表を作成できます。空間情報は、ウェル・ノウン形式またはカスタム形式にすることができます。

Oracle外部表を作成するために使用するHive表は、「Oracle Loader for Hadoopによって生成されたファイルを使用したOracle SQL Connector for HDFSの使用」で説明されているように作成する必要があります。

Hive表Oracleを使用して作成できるOracle外部表を作成します。次に例を示します。

CREATE TABLE SAMPLE_TWEETS (id VARCHAR2(4000),  geometry VARCHAR2(4000),  followers_count VARCHAR2(4000),  friends_count VARCHAR2(4000),  location VARCHAR2(4000), user_id VARCHAR2(4000))  ORGANIZATION EXTERNAL
(TYPE ORACLE_HIVE
 DEFAULT DIRECTORY DEFAULT_DIR 
 ACCESS PARAMETERS (
com.oracle.bigdata.cluster=cluster
com.oracle.bigdata.tablename=default.CUST_TWEETS_HIVE_TAB)
) PARALLEL 2 REJECT LIMIT UNLIMITED;

これで、表SAMPLE_TWEETSに対して問い合せることが可能になります。データベース内の他の任意の表と同じように問い合せることができます。次に例を示します。

select count(*) from SAMPLE_TWEETS;

この表に対して次の例のような空間操作を実行し、映画館の半径4分の1マイル以内でツイートしているユーザーを取得できます。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, SAMPLE_TWEETS tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_GEOMETRY(tw.geometry, 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';

この情報をさらに使用して広告カスタマイズすることもできます。

ジオメトリのSRIDは8307です。また、空間データがGeoJSON形式である場合、問合せは次のようにする必要があります。

select sdo_geom.SDO_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 0.5, 'UNIT=YARD'), ci.name, tw.user_id 
from CINEMA ci, SAMPLE_TWEETS tw where SDO_WITHIN_DISTANCE(ci.geometry, SDO_UTIL.FROM_GEOJSON(tw.geometry, '', 8307), 'DISTANCE=200 UNIT=MILE') = 'TRUE';