Tables

1: Avrotize k2a tool
2: Tables management

1 - Avrotize k2a tool

Learn how to use the Avrotize k2a command to connect to a Kusto database and create an Avro schema.

Avrotize is a versatile tool for converting data and database schema formats, and generating code in various programming languages. The tool supports the conversion of Kusto table schemas to Apache Avro format and vice versa with the Convert Kusto table definition to Avrotize Schema command. The tool handles dynamic columns in Kusto tables by:

Inferring the schema through sampling
Resolving arrays and records at any level of nesting
Detecting conflicting schemas
Creating type unions for each different schema branch

Convert table definition to AVRO format

You can use the avrotize k2a command to connect to a Kusto database and create an Avro schema with a record type for each of the tables in the database.

The following are examples of how to use the command:

Create an Avro schema with a top-level union with a record for each table:

avrotize k2a --kusto-uri <Uri> --kusto-database <DatabaseName> --avsc <AvroFilename.avsc>

Create a XRegistry Catalog file with CloudEvent wrappers and per-event schemas:
In the following example, you create xRegistry catalog files with schemas for each table. If the input table contains CloudEvents identified by columns like id, source, and type, the tool creates separate schemas for each event type.
```
avrotize k2a --kusto-uri <URI> --kusto-database <DatabaseName> --avsc <AvroFilename.xreg.json> --emit-cloudevents-xregistry --avro-namespace <AvroNamespace>
```

Convert AVRO schema to Kusto table declaration

You can use the avrotize a2k command to create KQL table declarations from Avro schema and JSON mappings. It can also include docstrings in the table declarations extracted from the “doc” annotations in the Avro record types.

If the Avro schema is a single record type, the output script includes a .create table command for the record. The record fields are converted into columns in the table. If the Avro schema is a type union (a top-level array), the output script emits a separate .create table command for each record type in the union.

avrotize a2k  .\<AvroFilename.avsc> --out <KustoFilename.kql>

The Avrotize tool is capable of converting JSON Schema, XML Schema, ASN.1 Schema, and Protobuf 2 and Protobuf 3 schemas into Avro schema. You can first convert the source schema into an Avro schema to normalize it and then convert it into Kusto schema.

For example, to convert “address.json” into Avro schema, the following command first converts an input JSON Schema document “address.json” to normalize it:

avrotize j2a address.json --out address.avsc

Then convert the Avro schema file into Kusto schema:

avrotize a2k address.avsc --out address.kql

You can also chain the commands together to convert from JSON Schema via Avro into Kusto schema:

avrotize j2a address.json | avrotize a2k --out address.kql

2 - Tables management

Learn how to use table management commands to display, create, and alter tables.

This topic discusses the life cycle of tables and associated management commands that are helpful for exploring, creating and altering tables.

Select the links in the table below for more information about them.

For information on optimizing table schema, see Schema optimization best practices.

Commands	Operation
`.alter table docstring`, `.alter table folder`	Manage table display properties
`.create ingestion mapping`, `.show ingestion mappings`, `.alter ingestion mapping`, `.drop ingestion mapping`	Manage ingestion mapping
`.create tables`, `.create table`, `.create-merge tables`, `.create-merge table`, `.alter table`, `.alter-merge table`, `.drop tables`, `.drop table`, `.undo drop table`, `.rename table`	Create/modify/drop tables
`.show tables` `.show table details.show table schema`	Enumerate tables in a database
`.ingest`, `.set`, `.append`, `.set-or-append` (see Data ingestion overview).	Data ingestion into a table
`.clear table data`	Clears all the data of a table

CRUD naming conventions for tables

(See full details in the sections linked to in the table, above.)