CrateDB Cloud News: Schema Evolution

CrateDB Cloud
1

We’re excited to announce a significant enhancement to the CrateDB Cloud import functionality: the introduction of Schema Evolution. This new feature is designed to make your initial data integration process easier and more efficient, especially when dealing with multiple files and changing data structures.

Until now, CrateDB Cloud has efficiently inferred table schemas directly from the files you import. This process has streamlined data integration, making it straightforward to create tables based on the structure of your imported data. However, as we continuously aim to improve your experience and adapt to your evolving data needs, we’re taking this a step further.

What’s New: Schema Evolution

With the latest update, CrateDB Cloud now supports the import of multiple files simultaneously, and more importantly, allows for schema evolution. This means that when you import multiple files at once, CrateDB will consider all files collectively to evolve the table schema appropriately.

  • Dynamic Schema Updates: When importing new files into an existing table, if these files have a slightly different schema (like additional columns), CrateDB Cloud now automatically updates the table schema. It adds the necessary columns on demand during the import process, sparing you the need to alter the table manually.
  • Flexibility and Control: We understand that in some scenarios, you might prefer to keep your table schema static. That’s why we’ve made Schema Evolution an optional feature. You can easily activate or deactivate it based on your specific requirements.

image
image2276×1030 134 KB

How it works in practice

Let’s explore how this feature functions using your provided JSON examples:

  1. First JSON (people_01.json)
{"id": 123, "name": "Georg"}
  1. Second JSON (people_02.json)
{"id": 123, "name": "Peter", "age": 34}

Initial Import:

  • When the first JSON ({"id": 123, "name": "Georg"}) is imported, CrateDB Cloud will infer and create a table schema based on this data. In this case, the schema will consist of two columns:
    • id: inferred as an BIGINT type.
    • name: Inferred as a TEXT type.
cr> SHOW CREATE TABLE my_import;
+-----------------------------------------------------+
| SHOW CREATE TABLE doc.my_import                     |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."my_import" (      |
|    "id" BIGINT,                                     |
|    "name" TEXT,                                     |
| )                                                   |
+-----------------------------------------------------+

Schema Evolution during Second Import:

  • Upon importing the second JSON ({"id": 123, "name": "Peter", "age": 34}), CrateDB Cloud’s Schema Evolution feature comes into play. This JSON introduces a new field age that wasn’t present in the first JSON.
  • Instead of rejecting this new data or requiring a manual update to the table schema, CrateDB Cloud will automatically alter the table schema to accommodate this new field.
  • The table schema post-import will now consist of three columns:
    • id: BIGINT type.
    • name: TEXT type.
    • age: This new column is inferred to be an BIGINT type based on the provided data.

Final Table Schema:

After the import of both JSON files, the table schema in CrateDB Cloud will look like this:

cr> SHOW CREATE TABLE my_import;
+-----------------------------------------------------+
| SHOW CREATE TABLE doc.my_import                     |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."my_import" (      |
|    "id" BIGINT,                                     |
|    "name" TEXT,                                     |
|    "age" BIGINT                                     |
| )                                                   |
+-----------------------------------------------------+

We’re thrilled to bring this new functionality to CrateDB Cloud, and we believe it will significantly enhance your data handling capabilities. As always, we’re keen to hear your feedback and experiences, so feel free to share your thoughts and stories in our community forum.

:cloud: Start using CrateDB Cloud for free :cloud:

4 Likes