AnalyticDB:Scriptella

Langkah 1: Konfigurasikan file ETL

Buat file etl.xml dengan konten berikut. Ganti nilai url, user, dan password dengan detail koneksi AnalyticDB for MySQL Anda.

<!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" />

    <!-- DROP TABLE -->
    <script connection-id="adb">
        drop table if exists `student_etl`;
    </script>

    <!-- CREATE TABLE -->
    <script connection-id="adb">
        Create Table `student_etl` (`id` bigint, `name` varchar, `unit` int ) DISTRIBUTED BY HASH(`id`) INDEX_ALL='Y';
    </script>

    <!-- QUERY -->
    <query connection-id="adb">
        SELECT * FROM student
    </query>

    <!-- INSERT TABLE -->
    <script connection-id="adb">
        insert into student_etl select * from student;
    </script>

    <!-- CREATE VIEW -->
    <script connection-id="adb">
        create view student_view as select * from student;
    </script>
</etl>

Klausa DISTRIBUTED BY HASH dan INDEX_ALL merupakan sintaks DDL khusus AnalyticDB for MySQL.

Langkah 2: Jalankan file ETL

java -jar scriptella.jar -debug etl.xml

Langkah 3: Verifikasi output

Eksekusi yang berhasil menghasilkan output seperti berikut:

2019-12-4 15:02:31 <Progress> Execution Progress.Initializing properties: 1%
2019-12-4 15:02:31 <Details> registerDriver: com.mysql.jdbc.Driver@6f539caf
2019-12-4 15:02:31 <Details> Found driver class com.mysql.jdbc.Driver
2019-12-4 15:02:31 <Details> DriverManager.getConnection("jdbc:mysql://127.0.0.1:3303/test4dmp")
2019-12-4 15:02:31 <Details>     trying com.mysql.jdbc.Driver
2019-12-4 15:02:32 <Details> getConnection returning com.mysql.jdbc.Driver
2019-12-4 15:02:32 <Details> 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 <Progress> Execution Progress.Initialized connection JdbcConnection{com.mysql.jdbc.JDBC4Connection}, Dialect{MySQL 5.1.35-analyticdb}, properties {}: 5%
2019-12-4 15:02:32 <Progress> Execution Progress./etl/script[2] prepared: 6%
2019-12-4 15:02:32 <Progress> Execution Progress./etl/script[3] prepared: 7%
2019-12-4 15:02:32 <Progress> Execution Progress./etl/script[4] prepared: 10%
2019-12-4 15:02:32 <Progress> Registered JMX mbean: scriptella:type=etl,url="file:/Library/scriptella-1.2/etl.xml"
2019-12-4 15:02:32 <Details> Executing script /etl/script[1]
2019-12-4 15:02:33 <Details>      Executed statement drop table if exists `student_etl`. Update count: 0
2019-12-4 15:02:33 <Details> Script /etl/script[1] completed
2019-12-4 15:02:33 <Progress> Execution Progress./etl/script[1] executed: 27%
2019-12-4 15:02:33 <Details> Executing script /etl/script[2]
2019-12-4 15:02:34 <Details>      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 <Details> Script /etl/script[2] completed
2019-12-4 15:02:34 <Progress> Execution Progress./etl/script[2] executed: 44%
2019-12-4 15:02:34 <Details> Executing query /etl/query[1]
2019-12-4 15:02:34 <Details> Processing row #1 for query /etl/query[1]
2019-12-4 15:02:34 <Details> Processing row #2 for query /etl/query[1]
2019-12-4 15:02:34 <Details>      Executed statement SELECT * FROM student
2019-12-4 15:02:34 <Details> Query /etl/query[1] processed.
2019-12-4 15:02:34 <Progress> Execution Progress./etl/query[1] executed: 61%
2019-12-4 15:02:34 <Details> Executing script /etl/script[3]
2019-12-4 15:02:34 <Details>      Executed statement insert into student_etl select * from student. Update count: 2
2019-12-4 15:02:34 <Details> Script /etl/script[3] completed
2019-12-4 15:02:34 <Progress> Execution Progress./etl/script[3] executed: 78%
2019-12-4 15:02:34 <Details> Executing script /etl/script[4]
2019-12-4 15:02:35 <Details>      Executed statement create view student_view as select * from student. Update count: 0
2019-12-4 15:02:35 <Details> Script /etl/script[4] completed
2019-12-4 15:02:35 <Progress> Execution Progress./etl/script[4] executed: 95%
2019-12-4 15:02:35 <Progress> Execution Progress.Complete
2019-12-4 15:02:35 <Details> Commiting connection JdbcConnection{com.mysql.jdbc.JDBC4Connection}
2019-12-4 15:02:35 <Details> Closing JdbcConnection{com.mysql.jdbc.JDBC4Connection}
2019-12-4 15:02:35 <Progress> 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 <Progress> Successfully executed ETL file /Library/scriptella-1.2/etl.xml

Kelima pernyataan tersebut dieksekusi dengan sukses. Baris terakhir Successfully executed ETL file mengonfirmasi bahwa eksekusi selesai tanpa error.