ru.yandex.clickhouse.ClickHouseDataSource Java Examples

@Test
public void testErrorDecompression() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setCompress(true);
    DataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    Connection connection = dataSource.getConnection();

    connection.createStatement().execute("DROP TABLE IF EXISTS test.table_not_exists");

    PreparedStatement statement = connection.prepareStatement("INSERT INTO test.table_not_exists (d, s) VALUES (?, ?)");

    statement.setDate(1, new Date(System.currentTimeMillis()));
    statement.setInt(2, 1);
    try {
        statement.executeBatch();
    } catch (Exception e) {
        Assert.assertTrue(getClickhouseException(e).getMessage().startsWith("ClickHouse exception, code: 60, host: localhost, port: 8123; Code: 60, e.displayText() = DB::Exception: Table test.table_not_exists doesn't exist."));
        return;
    }
    Assert.assertTrue(false, "didn' find correct error");
}
 

@Test
public void testResultSetWithExtremes() throws SQLException {
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setExtremes(true);
    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    Connection connection = dataSource.getConnection();

    try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("select 1");
        StringBuilder sb = new StringBuilder();
        while (rs.next()) {
            sb.append(rs.getString(1)).append("\n");
        }

        Assert.assertEquals(sb.toString(), "1\n");
    } finally {
        connection.close();
    }
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@Test
public void additionalParametersTest_clickhouse_datasource() {
    ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:1234/ppc?compress=1&decompress=1&user=root");

    assertTrue(clickHouseDataSource.getProperties().isCompress());
    assertTrue(clickHouseDataSource.getProperties().isDecompress());
    assertEquals("root", clickHouseDataSource.getProperties().getUser());
}
 

@Test
public void maxMemoryUsageParamShouldBeParsed() throws Exception {
    final Properties driverProperties = new Properties();
    driverProperties.setProperty("max_memory_usage", "42");

    ClickHouseDataSource ds = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/test", driverProperties);
    Assert.assertEquals(ds.getProperties().getMaxMemoryUsage(), Long.valueOf(42L), "max_memory_usage is missing");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
    dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    dateFormat.setTimeZone(TimeZone.getDefault());
}
 

@Test(expectedExceptions = ClickHouseException.class)
public void testTableNotExists() throws SQLException {
    ClickHouseProperties properties = new ClickHouseProperties();
    DataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    statement.execute("select * from table_not_exists");
}
 

@Test
public void testWrongUser() {
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setUser("not_existing");
    DataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    try {
        Connection connection = dataSource.getConnection();
    } catch (Exception e) {
        Assert.assertEquals((getClickhouseException(e)).getErrorCode(), 516);
        return;
    }
    Assert.assertTrue(false, "didn' find correct error");
}
 

@Setup
public void setup() throws SQLException {
  connection = new ClickHouseDataSource(jdbcUrl(), properties()).getConnection();

  retryBackoff =
      FluentBackoff.DEFAULT
          .withMaxRetries(maxRetries())
          .withMaxCumulativeBackoff(maxCumulativeBackoff())
          .withInitialBackoff(initialBackoff());
}
 

@Test
public void clickHouseSimpleDataSource() {
    DbConfig dbConfig = new DbConfig();
    DataSource ds = dbConfig.clickHouseDataSource("host1", 42, "db", -1, new ClickHouseProperties(), null, null);
    Assert.assertTrue(ds instanceof ClickHouseDataSource);
    ClickHouseDataSource cds = (ClickHouseDataSource) ds;
    Assert.assertEquals("host1", cds.getHost());
    Assert.assertEquals("db", cds.getDatabase());
    Assert.assertEquals(42, cds.getPort());
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setDecompress(true);
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeMethod
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setDecompress(true);
    properties.setCompress(true);
    connection = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties).getConnection();
    statement = connection.createStatement();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
    connection.createStatement().execute("DROP TABLE IF EXISTS test.writer");
    connection.createStatement().execute("CREATE TABLE test.writer (id Int32, name String) ENGINE = Log");
    connection.createStatement().execute("TRUNCATE TABLE test.writer");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseDataSource datasourceServerTz = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", new ClickHouseProperties());
    connectionServerTz = datasourceServerTz.getConnection();
    TimeZone serverTimeZone = connectionServerTz.getTimeZone();
    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setUseServerTimeZone(false);
    int serverTimeZoneOffsetHours = (int) TimeUnit.MILLISECONDS.toHours(serverTimeZone.getOffset(currentTime));
    int manualTimeZoneOffsetHours = serverTimeZoneOffsetHours - 1;
    properties.setUseTimeZone("GMT" + (manualTimeZoneOffsetHours > 0 ? "+" : "")  + manualTimeZoneOffsetHours + ":00");
    ClickHouseDataSource dataSourceManualTz = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connectionManualTz = dataSourceManualTz.getConnection();

    connectionServerTz.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
    connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
}
 

@Test
public void testArrayOfNullable() throws Exception {
    connection.createStatement().execute("DROP TABLE IF EXISTS test.array_of_nullable");
    connection.createStatement().execute(
            "CREATE TABLE IF NOT EXISTS test.array_of_nullable (" +
                    "str Nullable(String), " +
                    "int Nullable(Int32), " +
                    "strs Array(Nullable(String)), " +
                    "ints Array(Nullable(Int32))) ENGINE = TinyLog"
    );

    PreparedStatement statement = connection.prepareStatement(
            "INSERT INTO test.array_of_nullable (str, int, strs, ints) VALUES (?, ?, ?, ?)"
    );

    statement.setObject(1, null);
    statement.setObject(2, null);
    statement.setObject(3, new String[]{"a", null, "c"});
    statement.setArray(4, new ClickHouseArray(ClickHouseDataType.Int32, new Integer[]{1, null, 3}));
    statement.addBatch();
    statement.executeBatch();

    ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM test.array_of_nullable");

    Assert.assertTrue(rs.next());
    Assert.assertNull(rs.getObject("str"));
    Assert.assertNull(rs.getObject("int"));
    Assert.assertEquals(rs.getArray("strs").getArray(), new String[]{"a", null, "c"});
    Assert.assertEquals(rs.getArray("ints").getArray(), new int[]{1, 0, 3});
    Assert.assertFalse(rs.next());

    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setUseObjectsInArrays(true);
    ClickHouseDataSource configuredDataSource = new ClickHouseDataSource(dataSource.getUrl(), properties);
    ClickHouseConnection configuredConnection = configuredDataSource.getConnection();

    try {
        rs = configuredConnection.createStatement().executeQuery("SELECT * FROM test.array_of_nullable");
        rs.next();

        Assert.assertEquals(rs.getArray("ints").getArray(), new Integer[]{1, null, 3});
    } finally {
        configuredConnection.close();
    }
}
 

/**
 * Returns {@link TableSchema} for a given table.
 *
 * @param jdbcUrl jdbc connection url
 * @param table table name
 * @return table schema
 */
public static TableSchema getTableSchema(String jdbcUrl, String table) {
  List<TableSchema.Column> columns = new ArrayList<>();

  try (ClickHouseConnection connection = new ClickHouseDataSource(jdbcUrl).getConnection();
      Statement statement = connection.createStatement()) {

    ResultSet rs = null; // try-finally is used because findbugs doesn't like try-with-resource
    try {
      rs = statement.executeQuery("DESCRIBE TABLE " + quoteIdentifier(table));

      while (rs.next()) {
        String name = rs.getString("name");
        String type = rs.getString("type");
        String defaultTypeStr = rs.getString("default_type");
        String defaultExpression = rs.getString("default_expression");

        ColumnType columnType = ColumnType.parse(type);
        DefaultType defaultType = DefaultType.parse(defaultTypeStr).orElse(null);

        Object defaultValue;
        if (DefaultType.DEFAULT.equals(defaultType)
            && !Strings.isNullOrEmpty(defaultExpression)) {
          defaultValue = ColumnType.parseDefaultExpression(columnType, defaultExpression);
        } else {
          defaultValue = null;
        }

        columns.add(TableSchema.Column.of(name, columnType, defaultType, defaultValue));
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
    }

    return TableSchema.of(columns.toArray(new TableSchema.Column[0]));
  } catch (SQLException e) {
    throw new RuntimeException(e);
  }
}
 

@Test
public void constructorShouldNotIgnoreClickHouseProperties() {
    int expectedConnectionTimeout = 1000;
    boolean isCompress = false;
    Integer maxParallelReplicas = 3;
    Integer maxPartitionsPerInsertBlock = 200;
    Long maxInsertBlockSize = 142L;
    Boolean insertDeduplicate = true;
    Boolean insertDistributedSync = true;
    Boolean anyJoinDistinctRightTableKeys = true;

    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setConnectionTimeout( expectedConnectionTimeout );
    properties.setMaxParallelReplicas( maxParallelReplicas );
    properties.setMaxPartitionsPerInsertBlock( maxPartitionsPerInsertBlock );
    properties.setCompress( isCompress );
    properties.setMaxInsertBlockSize(maxInsertBlockSize);
    properties.setInsertDeduplicate(insertDeduplicate);
    properties.setInsertDistributedSync(insertDistributedSync);
    properties.setAnyJoinDistinctRightTableKeys(anyJoinDistinctRightTableKeys);

    ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(
            "jdbc:clickhouse://localhost:8123/test",
            properties
    );
    Assert.assertEquals(
            clickHouseDataSource.getProperties().getConnectionTimeout(),
            expectedConnectionTimeout
    );
    Assert.assertEquals(
            clickHouseDataSource.getProperties().isCompress(),
            isCompress
    );
    Assert.assertEquals(
            clickHouseDataSource.getProperties().getMaxParallelReplicas(),
            maxParallelReplicas
    );
    Assert.assertEquals(
            clickHouseDataSource.getProperties().getMaxPartitionsPerInsertBlock(),
            maxPartitionsPerInsertBlock
    );
    Assert.assertEquals(
            clickHouseDataSource.getProperties().getTotalsMode(),
            ClickHouseQueryParam.TOTALS_MODE.getDefaultValue()
    );
    Assert.assertEquals(
        clickHouseDataSource.getProperties().getMaxInsertBlockSize(),
        maxInsertBlockSize
    );
    Assert.assertEquals(
        clickHouseDataSource.getProperties().getInsertDeduplicate(),
        insertDeduplicate
    );
    Assert.assertEquals(
        clickHouseDataSource.getProperties().getInsertDistributedSync(),
        insertDistributedSync
    );
    Assert.assertEquals(
        clickHouseDataSource.getProperties().getAnyJoinDistinctRightTableKeys(),
        anyJoinDistinctRightTableKeys
    );
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
}
 

@BeforeTest
public void setUp() throws Exception {
    ClickHouseProperties properties = new ClickHouseProperties();
    dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123", properties);
    connection = dataSource.getConnection();
}