CREATE TABLE
Arroyo’s CREATE TABLE statements come in three flavors: Create Table As, Memory Table and Connection Table.CREATE TABLE AS
This command creates a table from the query included in it, e.g.CREATE TABLE (In-Memory)
CREATE TABLE statements without any connection info are presumed to be in memory. It can be written to within the same query context and then read from. A memory table may be used in only oneINSERT INTO
statement, but can then be
selected from multiple times. For example, you could create an orders
table
with a statement like
CREATE VIEW
is simply an alias for creating a memory table.
CREATE TABLE (Connection)
Connection tables allow Arroyo to read and write to external systems like Kafka clusters. Connection tables may be used as sources or sinks depending on the type of connection. For details on all of the supported connectors, see the Connectors docs. Connection tables can be created via the Connections tabs of the Web UI, or directly in SQL via theCREATE TABLE
statement.
Connection tables are created via special CREATE TABLE
statements that include
a WITH
clause. The WITH
clause specifies the connector, the
format that the data is encoded with, and various other
options that are specific to the connector, as documented on the individual
connector pages. The general form of the statement is:
connector
is one of the connectors documented here and
format is one of of the supported formats.
For example, to create a Kafka source for the topic order_topic
:
Schema inference
When creating a connection table, you can specify the schema explicitly by listing fields in the CREATE TABLE statement, or you can let Arroyo infer the schema from how it’s used. This is mostly relevant for sinks, where the schema can be inferred from the query that writes to the table. For example, a Kafka sink could be created like thisresults
topic
as
to ensure they
are what you expect.
Options
Connection tables allow you to configure a number of options that specify and modify the behavior. They are specified via theWITH
clause, with an unquoted
key and a single or double-quoted value. The following options are supported
across all connections. Specific connections have their own options. To see all
of the supported options, refer to the Connector docs.
Option | required | Description |
---|---|---|
connector | yes | The name of the connector to use. |
format | if connector does not have a built-in format | The format of the data to be deserialized. |
idle_micros | no | The number of microseconds to wait before considering a source idle. Defaults to 30000000 (30 seconds). Set to -1 to disable source idleness. |
Virtual Fields
Virtual fields can be created within theCREATE TABLE
statement. These are
done using the GENERATED ALWAYS AS (expression)
syntax. expression
must be a
valid Arroyo SQL expression that only depends on non-virtual fields within the
table. For example
INSERT INTO
Arroyo supports INSERT INTO statements for both memory and connection tables. In line with standard SQL the insertion will happen column-wise, attempting coercion to the SQL types. For example, if you have a memory tableorders
with
columns customer_id
and order_id
you could insert into it with a statement
like