Scriptella と AnalyticDB for MySQL の互換性 - AnalyticDB - Alibaba Cloud ドキュメントセンター

このドキュメントでは、Scriptella と AnalyticDB for MySQL との接続、テーブル作成、データクエリ、データ書き込み、ビュー作成に関する互換性について説明します。

テスト環境

Java	JDBC	Scriptella
Java バージョン 1.8.0_231	JDBC バージョン 5.1.48	Scriptella バージョン 1.2 (Scriptella から入手可能)

テスト範囲

コマンド
```
java -jar scriptella.jar -debug etl.xml
```

ETL スクリプト

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection id="adb" driver="mysql" url="jdbc:mysql://127.0.0.1:3303/test4dmp" user="k*****" password="" classpath="/Library/scriptella-1.2/mysql-connector-java-5.1.48.jar;/Library/scriptella-1.2/mysql-connector-java-5.1.48-bin.jar" />
    <!-- テーブルを削除 -->
    <script connection-id="adb">
        drop table if exists `student_etl`;
    </script>
    <!-- テーブルを作成 -->
    <script connection-id="adb">
        Create Table `student_etl` (`id` bigint, `name` varchar, `unit` int ) DISTRIBUTED BY HASH(`id`) INDEX_ALL='Y';
    </script>
    <!-- クエリ -->
    <query connection-id="adb">
        SELECT * FROM student
    </query>
    <!-- テーブルに挿入 -->
    <script connection-id="adb">
        insert into student_etl select * from student;
    </script>
    <!-- ビューを作成 -->
    <script connection-id="adb">
        create view student_view as select * from student;
    </script>
</etl>

ログ出力

2019-12-4 15:02:31 <INFO> Execution Progress.Initializing properties: 1%
2019-12-4 15:02:31 <DETAIL> registerDriver: com.mysql.jdbc.Driver@6f539caf
2019-12-4 15:02:31 <DETAIL> Found driver class com.mysql.jdbc.Driver
2019-12-4 15:02:31 <DETAIL> DriverManager.getConnection("jdbc:mysql://127.0.0.1:3303/test4dmp")
2019-12-4 15:02:31 <DETAIL>     trying com.mysql.jdbc.Driver
2019-12-4 15:02:32 <DETAIL> getConnection returning com.mysql.jdbc.Driver
2019-12-4 15:02:32 <DETAIL> jdbc:mysql://127.0.0.1:3303/test4dmp: Statement cache is enabled (cache size 64). Statement separator ';'. Autocommit: false.
2019-12-4 15:02:32 <INFO> Execution Progress.Initialized connection JdbcConnection{com.mysql.jdbc.JDBC4Connection}, Dialect{MySQL 5.1.35-analyticdb}, properties {}: 5%
2019-12-4 15:02:32 <INFO> Execution Progress./etl/script[2] prepared: 6%
2019-12-4 15:02:32 <INFO> Execution Progress./etl/script[3] prepared: 7%
2019-12-4 15:02:32 <INFO> Execution Progress./etl/script[4] prepared: 10%
2019-12-4 15:02:32 <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/Library/scriptella-1.2/etl.xml"
2019-12-4 15:02:32 <DETAIL> Executing script /etl/script[1]
2019-12-4 15:02:33 <DETAIL>      Executed statement drop table if exists `student_etl`. Update count: 0
2019-12-4 15:02:33 <DETAIL> Script /etl/script[1] completed
2019-12-4 15:02:33 <INFO> Execution Progress./etl/script[1] executed: 27%
2019-12-4 15:02:33 <DETAIL> Executing script /etl/script[2]
2019-12-4 15:02:34 <DETAIL>      Executed statement Create Table `student_etl` (`id` bigint, `name` varchar, `unit` int ) DISTRIBUTED BY HASH(`id`) INDEX_ALL='Y'. Update count: 0
2019-12-4 15:02:34 <DETAIL> Script /etl/script[2] completed
2019-12-4 15:02:34 <INFO> Execution Progress./etl/script[2] executed: 44%
2019-12-4 15:02:34 <DETAIL> Executing query /etl/query[1]
2019-12-4 15:02:34 <DETAIL> Processing row #1 for query /etl/query[1]
2019-12-4 15:02:34 <DETAIL> Processing row #2 for query /etl/query[1]
2019-12-4 15:02:34 <DETAIL>      Executed statement SELECT * FROM student
2019-12-4 15:02:34 <DETAIL> Query /etl/query[1] processed.
2019-12-4 15:02:34 <INFO> Execution Progress./etl/query[1] executed: 61%
2019-12-4 15:02:34 <DETAIL> Executing script /etl/script[3]
2019-12-4 15:02:34 <DETAIL>      Executed statement insert into student_etl select * from student. Update count: 2
2019-12-4 15:02:34 <DETAIL> Script /etl/script[3] completed
2019-12-4 15:02:34 <INFO> Execution Progress./etl/script[3] executed: 78%
2019-12-4 15:02:34 <DETAIL> Executing script /etl/script[4]
2019-12-4 15:02:35 <DETAIL>      Executed statement create view student_view as select * from student. Update count: 0
2019-12-4 15:02:35 <DETAIL> Script /etl/script[4] completed
2019-12-4 15:02:35 <INFO> Execution Progress./etl/script[4] executed: 95%
2019-12-4 15:02:35 <INFO> Execution Progress.Complete
2019-12-4 15:02:35 <DETAIL> Commiting connection JdbcConnection{com.mysql.jdbc.JDBC4Connection}
2019-12-4 15:02:35 <DETAIL> Closing JdbcConnection{com.mysql.jdbc.JDBC4Connection}
2019-12-4 15:02:35 <INFO> Execution statistics:
Executed 1 query, 4 scripts, 5 statements
/etl/script[1]: Element successfully executed (1 statement). Working time 897 milliseconds. Avg throughput: 1.11 statements/sec.
/etl/script[2]: Element successfully executed (1 statement). Working time 821 milliseconds. Avg throughput: 1.22 statements/sec.
/etl/query[1]: Element successfully executed (1 statement). Working time 36 milliseconds. Avg throughput: 27.27 statements/sec.
/etl/script[3]: Element successfully executed (1 statement). Working time 702 milliseconds. Avg throughput: 1.42 statements/sec.
/etl/script[4]: Element successfully executed (1 statement). Working time 303 milliseconds. Avg throughput: 3.3 statements/sec.
Total working time: 3.17 seconds
2019-12-4 15:02:35 <INFO> Successfully executed ETL file /Library/scriptella-1.2/etl.xml

mysql> select * from student_etl;
+------+------+------+
| id   | name | unit |
+------+------+------+
|    1 | a    |    1 |
|    2 | b    |    2 |
+------+------+------+
2 rows in set (0.06 sec)
mysql> select * from student_view;
+------+------+------+
| ID   | NAME | UNIT |
+------+------+------+
|    2 | b    |    2 |
|    1 | a    |    1 |
+------+------+------+
2 rows in set (0.04 sec)