run update on HAWQ failed #1

interma · 2017-03-16T09:03:29Z

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.

The text was updated successfully, but these errors were encountered:

interma · 2017-03-17T05:49:11Z

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 · 2017-03-17T12:18:24Z

@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 · 2017-03-17T15:14:13Z

@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 · 2017-03-18T21:27:30Z

@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 · 2017-03-20T07:23:55Z

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 · 2017-03-20T15:01:18Z

@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 · 2017-03-22T02:59:34Z

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 · 2017-03-22T16:27:00Z

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

interma changed the title ~~run unit test failed~~ run update on HAWQ failed Mar 17, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

run update on HAWQ failed #1

run update on HAWQ failed #1

interma commented Mar 16, 2017

interma commented Mar 17, 2017 •

edited

tzolov commented Mar 17, 2017

tzolov commented Mar 17, 2017

tzolov commented Mar 18, 2017

interma commented Mar 20, 2017

tzolov commented Mar 20, 2017

interma commented Mar 22, 2017

tzolov commented Mar 22, 2017

run update on HAWQ failed #1

run update on HAWQ failed #1

Comments

interma commented Mar 16, 2017

interma commented Mar 17, 2017 • edited

tzolov commented Mar 17, 2017

tzolov commented Mar 17, 2017

tzolov commented Mar 18, 2017

interma commented Mar 20, 2017

tzolov commented Mar 20, 2017

interma commented Mar 22, 2017

tzolov commented Mar 22, 2017

interma commented Mar 17, 2017 •

edited