What problems will users face when writing SQL for IOX? how can the UI help them?

[wiedld]

UI support for flux query editing:

Flux support in the QX project focused around users whom had a certain sets of challenges:

• did not know their schema
  • soln: schema browser
• did not know how to write flux to get data from their schema:
  • soln: composition and schema injection
• did not know how to write flux to shape their data
  • soln: result options
• did not know the flux standard lib
  • soln: flux function browser

Summary: it was almost all about teaching flux.

.

Are SQL challenges different?

As we move into SQL support, should we really be recreating these same features for SQL? Because most users know SQL, but face other concerns.

• may not know their schema.
• don't know how to map the influx data model onto the SQL syntax.
  • e.g. bucket == database and measurement == table, etc.
• do not know what subset of SQL semantics is supported in influx.
• do not know that the influx WRITE path allows identifiers with capitalization, spaces, etc. But the READ path requires the use of quoted special identifiers.
• what other ways is our SQL dialect different?
• How is the query run (against an influxdata IOX backend) going to be different?
• Are we going to guide users to write certain styles of SQL, to make it more performant? Are their optimization skills (learned using other SQL databases) going to lead them on a performant path for influx SQL?

The hope is that we kick off a discussion about what's different, what SQL-specific problems we face...such that we can hopefully coalesce on a solution which is Influx SQL specific. (And not design holdovers from flux-specific challenges.)

[garylfowler]

One item of information we should be cognizant of: Not all SQL users will know how to properly form their select statement based on a timestamp (versus date and time columns they may have had with their relational databases. Both absolute times (without using a linux time converter app) and relative times. So while they may be well versed in SQL, they may have some difficulty time bounding their queries or at least creating performant queries if they omit a time range (which they may do if they don't know how to format it). So at least some teaching may still be desirable.

[garylfowler]

I must also respectfully challenge the conclusion posed: "Summary: It was almost all about teaching flux"

While teaching the user flux was certainly 1 important goal, there were many others, including:

• Showing & teaching the user about the shape of their data
• Representing the InfluxDB Data Model in a more understanding way
• Allowing for quick iteration on queries
• Helping the user create more performant queries
• Increasing the number of queries the user performs (PAYG)
• Improving the reliability of queries with large datasets

[wiedld]

💯 agreed. I was asking the question about whether our approach to these goals should be different, now that we no longer have the burden of needing to express an entire language for the user. For example, the user would need to know the entire form of how to filter a field (a.k.a. the syntax, the use of OR, etc) in flux. Whereas with SQL, the user only needs to know that the field == projection (a.k.a. column).

We solved the issue with flux using schema composition, which required much more engineering to do reliability. Does this need to be solved in the same way in SQL? A DESCRIBE <measurement>; call to IOx provides all the schema information to the user without engineering an entire schema composition and includes additional information too (such as types)...so is surfacing this information a better (and faster) alternative?

The way to encode a timerange in SQL is definitely a challenge to be solved -- I agree. Hopefully we can all chew on that one longer. 😸

This may make the implementations easier (which is good with the headcount change), if we can come up with simpler solutions.

[wiedld]

The performance of queries, and reliability of queries, is also a thing for us to consider in IOx sql. Hoping we can save time by not having to teach SQL, and instead use the engineering time to handle those last 2 considerations (which we did not have enough time to fully address with flux imho). Considering that the performance can impact our resource costs, hopefully more engineering time can be spent in teaching good habits there (based on any nuances of iox).