New Feature:
Migration
We’re excited to unveil our latest feature – Migration! With it, Waii takes a giant leap in enabling users to convert seamlessly between different dialects of SQL and even step beyond pure SQL (MQL, PigSQL, StreamingSQL, and more to come).
Migration is very much built on the core Waii capabilities. The way Waii handles schemas, constraints, UDFs, and semantic context (business definition, glossary, documentation) as well as the capability to compile and optimize the generated SQL are all at play when it comes to translating the queries with accuracy and speed.
Feature Highlights:
There are many differences when it comes to different database systems. We meticulously manage those between different SQL dialects. Here are some examples:
- Semi-structured and Nested Data: Snowflake uses DOT NOTATION for querying nested JSON data, e.g., SELECT value:username FROM table;, whereas Postgres employs the “→” and “—>” operators, like SELECT value-->'username' FROM table;. These cases are all handled automatically with Waii.
- Date and Time Handling: PostgreSQL performs date arithmetic using operations like + INTERVAL '5 days', whereas Snowflake accomplishes the same task using functions such as DATEADD('DAY', 5, CURRENT_DATE), showing variations in handling date arithmetic between these systems. We facilitate seamless transitions between these different approaches out of the box.
- User-Defined Functions (UDFs): Distinct functions like STRING_AGG() and ARRAY_AGG() in Postgres and LISTAGG() and ARRAY_CONSTRUCT() in Snowflake are all handled.
- SQL Structures: For instance, Snowflake accepts SELECT * FROM (SELECT * FROM table), while Postgres demands a subquery alias, like SELECT * FROM (SELECT * FROM table) AS alias. There are hundreds of differences like this, that are handled out of the box by Waii.
How to Use Migration:
The migration capability is available through API and CLI. Here’s an example:
- The command is waii query transcode --from <dialect> --to <dialect>
- Example 1 – Snowflake to Postgres:
- echo "SELECT value:username FROM users;" | waii query transcode --from snowflake --to postgres
SELECT value-->'username' FROM users;
- Example 2 – Postgres to MongoDB:
- echo "SELECT username, age FROM users WHERE age >= 21;" | waii query transcode --from postgres --to mongodb
db.users.find({ age: { $gte: 21 } }, { username: 1, age: 1 });
Conclusion:
This innovative update significantly amplifies Waii’s ability to solve data problems through AI, granting users unparalleled flexibility in SQL migration. We remain devoted to continuous advancements and eagerly anticipate your invaluable feedback. Happy transcoding!