Create table as select from stream?

[simper66]

Hi,

I have tried this:

InputStream in = new ByteArrayInputStream("JHON,1\nSMITH,3".getBytes());

client.connect(nodes).write().query("""

CREATE TABLE myTable ENGINE=LOG
AS SELECT * FROM input() FORMAT CSV

""").data(in).executeAndWait().close();

But with no luck.

I think "input" works with INSERT SELECT but not with CREATE TABLE AS SELECT

Ideally, it would be great to auto infer the datatypes.

Any ideas?

[zhicwu]

Hi @simper66, scheme inferring happens on server side and looks like it's currently not supported.

CREATE TABLE myTable ENGINE=Memory AS SELECT * FROM input('s String, i Int32') FORMAT CSV
...
Received exception from server (version 22.8.4):
Code: 477. DB::Exception: Received from localhost:9000. DB::Exception: Input stream is not initialized, input() must be used only in INSERT SELECT query. (INVALID_USAGE_OF_INPUT)

If you want to create a table from a stream in Java, I can only think of using external table at this point, for example:

try (ClickHouseResponse response = client.connect(server).write()
                .external(ClickHouseExternalTable.builder().name("myExtTable").addColumn("s", "String")
                        .addColumn("i", "Int32").content(ClickHouseInputStream.of("one,1\ntwo,2"))
                        .format(ClickHouseFormat.CSV).build())
                .query("create table test_create_table_as_select engine=Memory as select * from myExtTable")
                .executeAndWait()) {
    ClickHouseResponseSummary summary = response.getSummary();
    Assert.assertEquals(summary.getWrittenRows(), 2L);
}

[simper66]

Good to know, thank you @zhicwu