AnalyticDB:Scriptella

Test environment

Test scope

• Command

  java -jar scriptella.jar -debug etl.xml

• ETL script

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

• Log output

  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)