ru.yandex.clickhouse.ClickHouseStatement Java Examples

@Benchmark
@Test
public void benchInsertHttpRowBinary() throws Exception {
    withConnection(connection -> {
        wideColumnPrepare(connection, columnType);
        ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
        sth.write().send("INSERT INTO " + getTableName(), stream -> {
            for (int i = 0; i < batchSize; i++) {
                for (int j = 0; j < columnNum; j++ ) {
                    stream.writeString(j + 1 + "");
                }
            }
        }, ClickHouseFormat.RowBinary);

        wideColumnAfter(connection);
    }, ConnectionType.HTTP);
}
 

@Benchmark
@Test
public void benchInsertHttpRowBinary() throws Exception {
    withConnection(connection -> {
        wideColumnPrepare(connection, columnType);
        ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
        sth.write().send("INSERT INTO " + getTableName(), stream -> {
            for (int i = 0; i < batchSize; i++) {
                for (int j = 0; j < columnNum; j++ ) {
                    stream.writeInt32(j + 1);
                }
            }
        }, ClickHouseFormat.RowBinary);

        wideColumnAfter(connection);
    }, ConnectionType.HTTP);
}
 

@Benchmark
@Test
public void benchInsertHttpRowBinary() throws Exception {
    withConnection(connection -> {
        wideColumnPrepare(connection, columnType);
        ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
        sth.write().send("INSERT INTO " + getTableName(), stream -> {
            for (int i = 0; i < batchSize; i++) {
                for (int j = 0; j < columnNum; j++ ) {
                    stream.writeFloat64(j + 1.0);
                }
            }
        }, ClickHouseFormat.RowBinary);

        wideColumnAfter(connection);
    }, ConnectionType.HTTP);
}
 

@Test
public void testExternalData() throws SQLException, UnsupportedEncodingException {
    ClickHouseStatement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(
            "select UserName, GroupName " +
                    "from (select 'User' as UserName, 1 as GroupId) as g" +
                    "any left join groups using GroupId",
            null,
            Collections.singletonList(new ClickHouseExternalData(
                    "groups",
                    new ByteArrayInputStream("1\tGroup".getBytes())
            ).withStructure("GroupId UInt8, GroupName String"))
    );

    rs.next();

    String userName = rs.getString("UserName");
    String groupName = rs.getString("GroupName");

    Assert.assertEquals(userName, "User");
    Assert.assertEquals(groupName, "Group");
}
 

private boolean checkQuery(String queryId, boolean isRunning, long timeoutSecs) throws Exception {
    long start = System.currentTimeMillis();

    do {
        ClickHouseStatement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute(String.format("SELECT * FROM system.processes where query_id='%s'", queryId));
            ResultSet resultSet = statement.getResultSet();
            if (resultSet.next() == isRunning) {
                return true;
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
        }

    } while (TimeUnit.MILLISECONDS.toSeconds(System.currentTimeMillis() - start) < timeoutSecs);

    return false;
}
 

public ClickHouseResultSet(InputStream is, int bufferSize, String db, String table,
    boolean usesWithTotals, ClickHouseStatement statement, TimeZone timeZone,
    ClickHouseProperties properties) throws IOException
{
    this.db = db;
    this.table = table;
    this.statement = statement;
    this.properties = properties;
    this.usesWithTotals = usesWithTotals;
    this.dateTimeTimeZone = timeZone;
    this.dateTimeZone = properties.isUseServerTimeZoneForDates()
        ? timeZone
        : TimeZone.getDefault();
    dateTimeFormat.setTimeZone(dateTimeTimeZone);
    dateFormat.setTimeZone(dateTimeZone);
    bis = new StreamSplitter(is, (byte) 0x0A, bufferSize);  ///   \n
    ByteFragment headerFragment = bis.next();
    if (headerFragment == null) {
        throw new IllegalArgumentException("ClickHouse response without column names");
    }
    String header = headerFragment.asString(true);
    if (header.startsWith("Code: ") && !header.contains("\t")) {
        is.close();
        throw new IOException("ClickHouse error: " + header);
    }
    String[] cols = toStringArray(headerFragment);
    ByteFragment typesFragment = bis.next();
    if (typesFragment == null) {
        throw new IllegalArgumentException("ClickHouse response without column types");
    }
    String[] types = toStringArray(typesFragment);
    columns = new ArrayList<ClickHouseColumnInfo>(cols.length);
    for (int i = 0; i < cols.length; i++) {
        columns.add(ClickHouseColumnInfo.parse(types[i], cols[i]));
    }
}
 

@Test
public void testTimeZone() throws Exception{
    final ClickHouseStatement statement = connection.createStatement();
    connection.createStatement().execute("DROP TABLE IF EXISTS test.binary_tz");
    connection.createStatement().execute(
        "CREATE TABLE test.binary_tz (date Date, dateTime DateTime) ENGINE = MergeTree(date, (date), 8192)"
    );

    final Date date1 = new Date(1497474018000L);

    statement.sendRowBinaryStream(
        "INSERT INTO test.binary_tz (date, dateTime)",
        new ClickHouseStreamCallback() {
            @Override
            public void writeTo(ClickHouseRowBinaryStream stream) throws IOException {
                stream.writeDate(date1);
                stream.writeDateTime(date1);
            }
        }
    );

    ResultSet rs = connection.createStatement().executeQuery(
        "SELECT date, dateTime from test.binary_tz"
    );

    Assert.assertTrue(rs.next());
    assertEquals(rs.getTime("dateTime"), new Time(date1.getTime()));
    Date expectedDate = withTimeAtStartOfDay(date1); // expected start of the day in local timezone
    assertEquals(rs.getDate("date"), expectedDate);
}
 

@Test
public void testLowCardinality() throws Exception{
    final ClickHouseStatement statement = connection.createStatement();
    connection.createStatement().execute("DROP TABLE IF EXISTS test.low_cardinality");
    connection.createStatement().execute(
        "CREATE TABLE test.low_cardinality (date Date, lowCardinality LowCardinality(String), string String) ENGINE = MergeTree(date, (date), 8192)"
    );

    final Date date1 = new Date(1497474018000L);

    statement.sendNativeStream(
        "INSERT INTO test.low_cardinality (date, lowCardinality, string)",
        new ClickHouseStreamCallback() {
            @Override
            public void writeTo(ClickHouseRowBinaryStream stream) throws IOException {
                stream.writeUnsignedLeb128(3); // Columns number
                stream.writeUnsignedLeb128(1); // Rows number

                stream.writeString("date"); // Column name
                stream.writeString("Date");  // Column type
                stream.writeDate(date1);  // value

                stream.writeString("lowCardinality"); // Column name
                stream.writeString("String");  // Column type
                stream.writeString("string");  // value

                stream.writeString("string"); // Column name
                stream.writeString("String");  // Column type
                stream.writeString("string");  // value
            }
        }
    );

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

    Assert.assertTrue(rs.next());
    assertEquals(rs.getString("lowCardinality"), "string");
    assertEquals(rs.getString("string"), "string");
}
 

@Test
public void cancelTest_queryId_is_not_set() throws Exception {
    final ClickHouseStatement firstStatement = dataSource.getConnection().createStatement();

    final AtomicReference<Exception> exceptionAtomicReference = new AtomicReference<Exception>();
    Thread thread = new Thread() {
        @Override
        public void run() {
            try {
                Map<ClickHouseQueryParam, String> params = new EnumMap<ClickHouseQueryParam, String>(ClickHouseQueryParam.class);
                params.put(ClickHouseQueryParam.CONNECT_TIMEOUT, Long.toString(TimeUnit.MINUTES.toMillis(1)));
                firstStatement.executeQuery("SELECT count() FROM system.numbers", params);
            } catch (Exception e) {
                exceptionAtomicReference.set(e);
            }
        }
    };
    thread.setDaemon(true);
    thread.start();


    final long timeout = 10;
    String queryId = (String) readField(firstStatement, "queryId", timeout);
    assertNotNull(String.format("it's actually very strange. It seems the query hasn't been executed in %s seconds", timeout), queryId);
    assertNull("An exception happened while the query was being executed", exceptionAtomicReference.get());


    assertTrue("The query isn't being executed. It seems very strange", checkQuery(queryId, true,10));
    firstStatement.cancel();
    assertTrue("The query is still being executed", checkQuery(queryId, false, 10));

    firstStatement.close();
    thread.interrupt();
}
 

@Test
public void cancelTest_queryId_is_set() throws Exception {
    final String queryId = UUID.randomUUID().toString();
    final ClickHouseStatement firstStatement = dataSource.getConnection().createStatement();

    final CountDownLatch countDownLatch = new CountDownLatch(1);
    final AtomicReference<Exception> exceptionAtomicReference = new AtomicReference<Exception>();
    Thread thread = new Thread() {
        @Override
        public void run() {
            try {
                Map<ClickHouseQueryParam, String> params = new EnumMap<ClickHouseQueryParam, String>(ClickHouseQueryParam.class);
                params.put(ClickHouseQueryParam.CONNECT_TIMEOUT, Long.toString(TimeUnit.MINUTES.toMillis(1)));
                params.put(ClickHouseQueryParam.QUERY_ID, queryId);
                countDownLatch.countDown();
                firstStatement.executeQuery("SELECT count() FROM system.numbers", params);
            } catch (Exception e) {
                exceptionAtomicReference.set(e);
            }
        }
    };
    thread.setDaemon(true);
    thread.start();
    final long timeout = 10;
    assertTrue(String.format("it's actually very strange. It seems the query hasn't been executed in %s seconds", timeout), countDownLatch.await(timeout, TimeUnit.SECONDS));
    assertNull("An exception happened while the query was being executed", exceptionAtomicReference.get());

    assertTrue("The query isn't being executed. It seems very strange", checkQuery(queryId, true,10));
    firstStatement.cancel();
    assertTrue("The query is still being executed", checkQuery(queryId, false, 10));

    firstStatement.close();
    thread.interrupt();
}
 

private void flush() throws Exception {
  BackOff backOff = retryBackoff.backoff();
  int attempt = 0;

  if (buffer.isEmpty()) {
    return;
  }

  batchSize.update(buffer.size());

  while (true) {
    try (ClickHouseStatement statement = connection.createStatement()) {
      statement.sendRowBinaryStream(
          insertSql(schema(), table()),
          stream -> {
            for (Row row : buffer) {
              ClickHouseWriter.writeRow(stream, schema(), row);
            }
          });
      buffer.clear();
      break;
    } catch (SQLException e) {
      if (!BackOffUtils.next(Sleeper.DEFAULT, backOff)) {
        throw e;
      } else {
        retries.inc();
        LOG.warn(String.format(RETRY_ATTEMPT_LOG, attempt), e);
        attempt++;
      }
    }
  }
}
 

public ClickHouseScrollableResultSet(InputStream is, int bufferSize, String db, String table, boolean usesWithTotals, ClickHouseStatement statement, TimeZone timezone, ClickHouseProperties properties) throws IOException {
    super(is, bufferSize, db, table, usesWithTotals, statement, timezone, properties);
    lines = new ArrayList<ByteFragment[]>();
}
 

private static ClickHouseResultSet buildResultSet(InputStream is, int bufferSize, String db, String table, boolean usesWithTotals, ClickHouseStatement statement, TimeZone timezone, ClickHouseProperties properties) throws IOException {
	return new ClickHouseScrollableResultSet(is, bufferSize, db, table, usesWithTotals, statement, timezone, properties);
}
 

private static ClickHouseResultSet buildResultSet(InputStream is, int bufferSize, String db, String table, boolean usesWithTotals, ClickHouseStatement statement, TimeZone timezone, ClickHouseProperties properties) throws IOException {
	return new ClickHouseResultSet(is, bufferSize, db, table, usesWithTotals, statement, timezone, properties);
}