run update on HAWQ failed

[interma]

build on centos7 and with psql(hawq):

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
psql: invalid connection option "postgresql://localhost:5432/postgres?user"
java.io.IOException: Broken pipe
	at java.io.FileOutputStream.writeBytes(Native Method)
	at java.io.FileOutputStream.write(FileOutputStream.java:326)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at java.io.FilterOutputStream.close(FilterOutputStream.java:158)
	at org.apache.calcite.adapter.jdbc.TargetDatabase.rebuild(TargetDatabase.java:172)
	at org.apache.calcite.adapter.jdbc.TargetDatabase.<clinit>(TargetDatabase.java:29)
	at org.apache.calcite.adapter.jdbc.IntegrationBase.<init>(IntegrationBase.java:12)
	at org.apache.calcite.adapter.jdbc.DeleteBigintIntegrationTest.<init>(DeleteBigintIntegrationTest.java:9)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.junit.runners.BlockJUnit4ClassRunner.createTest(BlockJUnit4ClassRunner.java:217)
	at org.junit.runners.BlockJUnit4ClassRunner$1.runReflectiveCall(BlockJUnit4ClassRunner.java:266)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.BlockJUnit4ClassRunner.methodBlock(BlockJUnit4ClassRunner.java:263)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.apache.maven.surefire.junitcore.pc.Scheduler$1.run(Scheduler.java:393)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Running org.apache.calcite.adapter.jdbc.JournalledJdbcTableTest
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.628 sec - in org.apache.calcite.adapter.jdbc.JournalledJdbcTableTest

which's the problem? thanks.

[interma]

Don't care unit-test:mvn package -Dmaven.test.skip=true, compile and package is passed.

But when test update sql with HAWQ, failed:

//sql = "INSERT INTO hr.depts (deptno, department_name) VALUES(696, 'Pivotal')";

$ time java -jar journalled-sql-rewriter-example/target/journalled-sql-rewriter-example-1.3-SNAPSHOT.jar
Exception in thread "main" java.sql.SQLException: Error while executing SQL "UPDATE hr.depts SET department_name='interma' WHERE deptno = 696": while executing SQL [INSERT INTO "hr"."depts_journal" ("deptno", "department_name")
(SELECT "deptno", 'interma' AS "department_name"
FROM (SELECT "deptno", "version_number", "subsequent_version_number", MAX("version_number") OVER (PARTITION BY "deptno" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "$f4"
FROM "hr"."depts_journal") AS "t"
WHERE "version_number" = "$f4" AND "subsequent_version_number" IS NULL AND "deptno" = 696)]
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
	at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
	at io.pivotal.calcite.sqlrewriter.Main.main(Main.java:29)
Caused by: java.lang.RuntimeException: while executing SQL

not support hawq now?

[tzolov]

@interma Thanks for the feedback!
Primary target for the library is HAWQ so it has to be supported. Which version of HAWQ are you using?

Also have you created the depts_journal table on your HAWQ instance. You can test like this:

SELECT * FROM "hr"."depts_journal";

You create the schema and the table like this:

DROP SCHEMA IF EXISTS hr CASCADE;
CREATE SCHEMA hr;

CREATE TABLE hr.depts_journal (
  deptno                    SERIAL                   NOT NULL,
  version_number            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  subsequent_version_number TIMESTAMP WITH TIME ZONE NULL     DEFAULT NULL,
  department_name           TEXT                     NOT NULL,
  PRIMARY KEY (deptno, version_number)
);

[tzolov]

@interma I think i know what is the problem. If your version_number is of type BIGINT then you must set the "journalVersionType": "BIGINT" in the myTestModel.json. If not set it defaults to TIMESTAMP.

[tzolov]

@interma, please pull latest version of the project. We have added version column type check. If your model configuration doesn't match the column type an IllegalStateException will be thrown with related description and instructions how to resolve the problem. Hope this solves the issue

[interma]

Thanks @tzolov , but still have problems:

$ java -jar journalled-sql-rewriter-example/target/journalled-sql-rewriter-example-1.6-SNAPSHOT.jar
Exception in thread "main" java.lang.RuntimeException: Error instantiating JsonCustomSchema(name=hr)
        at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:220)
	at org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:45)
	at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:143)
	at org.apache.calcite.model.ModelHandler.<init>(ModelHandler.java:85)
	at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:104)
	at org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:145)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at io.pivotal.calcite.sqlrewriter.Main.main(Main.java:16)
Caused by: java.lang.RuntimeException: Property 'org.apache.calcite.adapter.jdbc.JournalledJdbcSchema$Factory' not valid for plugin type org.apache.calcite.schema.SchemaFactory
	at org.apache.calcite.avatica.AvaticaUtils.instantiatePlugin(AvaticaUtils.java:239)
	at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:211)
	... 8 more
Caused by: java.lang.ClassNotFoundException: org.apache.calcite.adapter.jdbc.JournalledJdbcSchema$Factory
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at org.apache.calcite.avatica.AvaticaUtils.instantiatePlugin(AvaticaUtils.java:227)
	... 9 more

my environment: macOs, the newest HAWQ(https://github.com/apache/incubator-hawq)

my test code:

$ cat journalled-sql-rewriter-example/src/main/java/io/pivotal/calcite/sqlrewriter/Main.java
package io.pivotal.calcite.sqlrewriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Main {
	public static void main(String[] argv) throws Exception {
		Class.forName(org.apache.calcite.jdbc.Driver.class.getName());
		Properties info = new Properties();
		String sql;
		info.setProperty("lex", "JAVA");
		info.setProperty("model", "journalled-sql-rewriter-example/src/main/resources/myTestModel.json");
		Connection calConnection = DriverManager.getConnection("jdbc:calcite:", info);

		Statement statement = calConnection.createStatement();
//		String sql = "SELECT d.deptno\n"
//				+ "FROM hr.emps AS e\n"
//				+ "JOIN hr.depts AS d\n"
//				+ "  ON e.deptno = d.deptno\n"
//				+ "GROUP BY d.deptno\n"
//				+ "HAVING count(*) > 1";


		sql = "UPDATE hr.depts SET department_name='interma' WHERE deptno = 696";
		statement.execute(sql);
		System.out.printf("ok");
		statement.close();
		calConnection.close();
		return;
		/*
		sql = "INSERT INTO hr.depts (deptno, department_name) VALUES(696, 'Pivotal')";

		if (statement.execute(sql)) {
			ResultSet results = statement.getResultSet();
			while (results.next()) {
				System.out.println(results.getInt(1));
			}
			results.close();
		} else {
			System.out.printf("Update count: " + statement.getUpdateCount());
		}

		statement.close();
		calConnection.close();
		*/
	}
}

[tzolov]

@interma it seems like you haven't listed all required dependencies when starting the Main. To simplify the execution i've made configured (in the pom) this as self executable jar. I've also updated the Main to test all 4 statements SELECT/INSERT/UPDATE/DELETE. and i've added instructions how to build and run the example: https://github.com/tzolov/calcite-sql-rewriter/tree/master/journalled-sql-rewriter-example

You can check also more elaborate SpringBoot based example here: https://github.com/tzolov/calcite-sql-rewriter/tree/master/sql-rewriter-springboot-example

Please let me know if this solved your problem? (Make sure to pull latest changes form the master)

[interma]

Nice! It works well (on HAWQ).

$ java -jar ./target/journalled-sql-rewriter-example-1.7-SNAPSHOT.jar src/main/resources/myTestModel.json
[main] INFO io.pivotal.calcite.sqlrewriter.Main - INSERT INTO hr.depts (deptno, department_name) VALUES(696, 'Pivotal')
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   result: 696 , Pivotal ,
[main] INFO io.pivotal.calcite.sqlrewriter.Main - UPDATE hr.depts SET department_name='interma' WHERE deptno = 696
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   result: 696 , interma ,
[main] INFO io.pivotal.calcite.sqlrewriter.Main - DELETE FROM hr.depts WHERE deptno = 696
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   updated rows: 1
[main] INFO io.pivotal.calcite.sqlrewriter.Main - SELECT * FROM hr.depts
[main] INFO io.pivotal.calcite.sqlrewriter.Main -   result:
[main] INFO io.pivotal.calcite.sqlrewriter.Main - Done

We plan to introduce UPDATE to HAWQ, and inspired by your works. If you have more ideas, welcome to comment at: https://issues.apache.org/jira/browse/HAWQ-304 Thanks!

[tzolov]

Glad to hear this @interma ! I've added some remarks to HAWQ-304