lowercase table names

[HaroonSaid]

When writing a pull query, how do I specify that the table name is in lowercase

return await this.context.CreatePullQuery<ElasticSearchEvent>("table_name")
                 .Where(c => c.Key == key)
                 .GetAsync();

converts to uppercase

[tomasfabian]

Hi @HaroonSaid, you have already specified the table name as lowercase in your example, didn't you?

int key = 42;
string query = context.CreatePullQuery<ElasticSearchEvent>(tableName: "table_name")
                 .Where(c => c.Key == key)
                 .ToQueryString();

Console.WriteLine(query);

The above mentioned code snippet should print the following query:

SELECT * FROM table_name
WHERE Key = 42;

What are your expectations, could you please clarify them more precisely?

For example if the table name is "elastic_search_event", you can provide it as the argument for CreatePullQuery(string tableName).

context.CreatePullQuery<ElasticSearchEvent>("elastic_search_event")

[HaroonSaid]

Hi Tomas,

Thanks for the quick reply, The query string is:

SELECT * FROM integration_test_ticketing_event_enriched_queryables\nWHERE Key = '1:2';

The exception that I see is

Exception while preparing statement: INTEGRATION_TEST_TICKETING_EVENT_ENRICHED_QUERYABLES does not exist.\nStatement: SELECT * FROM integration_test_ticketing_event_enriched_queryables\nWHERE Key = '1:2';\nCaused by: INTEGRATION_TEST_TICKETING_EVENT_ENRICHED_QUERYABLES does not exist.

The query should look like

SELECT * FROM "integration_test_ticketing_event_enriched_queryables"\nWHERE Key = '1:2';

Let me know if you need any more info.

[tomasfabian]

@HaroonSaid, in my environment with CLI the table name without quotation marks works and with them throws the exception as in your case.

ksql> CREATE OR REPLACE TABLE integration_test_ticketing_event_enriched_queryables
>AS SELECT key, Count(Key) FROM mytable
>GROUP BY Key EMIT CHANGES;

 Message
------------------------------------------------------------------------------------
 Created query with ID CTAS_INTEGRATION_TEST_TICKETING_EVENT_ENRICHED_QUERYABLES_17
------------------------------------------------------------------------------------
ksql> SELECT * FROM integration_test_ticketing_event_enriched_queryables
>WHERE Key = '1:2';
+----------------------------------------------------------+----------------------------------------------------------+
|KEY                                                       |KSQL_COL_0                                                |
+----------------------------------------------------------+----------------------------------------------------------+
Query terminated

ksql> SELECT * FROM "integration_test_ticketing_event_enriched_queryables"
>WHERE Key = '1:2';
Exception while preparing statement: integration_test_ticketing_event_enriched_queryables does not exist.
Statement: SELECT * FROM "integration_test_ticketing_event_enriched_queryables"
WHERE Key = '1:2';
Caused by: integration_test_ticketing_event_enriched_queryables does not exist.

ksql> SELECT * FROM integration_test_ticketing_event_enriched_queryables
>WHERE Key = '1:2';
+----------------------------------------------------------+----------------------------------------------------------+
|KEY                                                       |KSQL_COL_0                                                |
+----------------------------------------------------------+----------------------------------------------------------+
Query terminated

ksql> version
Version: 0.19.0

Check also this integration test please: https://github.com/tomasfabian/Kafka.DotNet.ksqlDB/blob/main/Tests/Kafka.DotNet.ksqlDB.IntegrationTests/KSql/Linq/PullQueries/PullQueryExtensionsTests.cs

[HaroonSaid]

Interesting - I am using version 2.0 of Ksqldb

Locally running Confluente Kafka.

[HaroonSaid]

If you create the table without double quotes, and show tables;

You will see it upper case name, that is how kafka does it - everything is uppercase unless in double quote

• Does it make sense to have an option on createquery to say casesensitive true|false?

[HaroonSaid]

We are running the following statements to create the table:

CREATE TYPE EventCategories AS STRUCT<id INTEGER, name VARCHAR, description VARCHAR>;

    CREATE TABLE IF NOT EXISTS "enrichedevents" ( 
        key VARCHAR PRIMARY KEY,
        id VARCHAR,
        ...
        eventCategories ARRAY<EventCategories>)
    WITH (KAFKA_TOPIC='enrichedevents', PARTITIONS=1, VALUE_FORMAT='JSON');

[tomasfabian]

@HaroonSaid I downgraded to version 0.9.0, it should be same as cp 2.0 based on this table, but the CREATE TABLE statement you provided me does not work in this version. I was able to run the following statements:

CREATE TYPE EventCategories AS STRUCT<id INTEGER, name VARCHAR, description VARCHAR>;

CREATE TABLE IF NOT EXISTS "enrichedevents" ( 
        key VARCHAR, //PRIMARY KEY (is not supported in this version)
        id VARCHAR,
        eventCategories ARRAY<EventCategories>)
WITH (KAFKA_TOPIC='enrichedevents', PARTITIONS=1, VALUE_FORMAT='JSON');

CREATE TABLE "integration_test_ticketing_event_enriched_queryables"
WITH ( KAFKA_TOPIC='Example03', VALUE_FORMAT='Json', PARTITIONS='1', REPLICAS='1' )
AS SELECT KEY, COUNT() FROM "enrichedevents"
GROUP BY KEY EMIT CHANGES;

SELECT * FROM `integration_test_ticketing_event_enriched_queryables` WHERE ROWKEY = '1';

Does one of these examples work for you? Table name surrounded with backticks or ToUpper()?

context.CreatePullQuery<ElasticSearchEvent>("`integration_test_ticketing_event_enriched_queryables`");

context.CreatePullQuery<ElasticSearchEvent>("integration_test_ticketing_event_enriched_queryables".ToUpper());

thank you for detailed explanations.

[HaroonSaid]

I was able to use backtick in the table name to solve my problem

Thanks