Yes, CrateDB can import JSON data and export data as JSON. When importing data into Crate, JSON is the only supported format.
You can export an Elasticsearch instance and import it into CrateDB. This is easy to do thanks to Crate’s dynamic schema update feature. After the data is loaded, you can read and write data in CrateDB via SQL. CrateDB also supports using the ES API to read, but not write data in Crate.
Yes, this is possible, read more about migrating data from MySQL (or most any other SQL DBMS) here:
CrateDB does not currently support foreign keys.
Crate introduced support for subselects in version 2.0.
CrateDB does not currently support triggers.
CrateDB does not currently support stored procedures.
CrateDB has a convenient CREATE SNAPSHOT SQL command that stores the current state of one or all tables into a backup store. There are drivers for several stores available, including local file system, an Amazon S3 storage, or HDFS.
MemSQL (as any in-memory DB) is great when your data size will fit into memory. Despite some mechanics to store overflowing data on disk, in reality this will negatively impact the performance and usability. While the raw query performance may be better with MemSql (as long as the data is in memory), Crate is close, but provides an ideal solution for all use cases where datasets will grow over time and a cost effective solution is required for storage as well as querying by adding commodity servers to match data growth.
Crate offers a level of SQL compatibility, but with improved speed and scalability.
The World of NoSQL databases is a large one, so we recommend you read our list of comparisons to other databases.
You can import data into Crate with normal INSERT statements (if you have large amounts of initial data, we have some extra tips how to ingest them), or, more efficiently, via COPY FROM statements. These can take JSON as an import format and create a dynamic schema.
Crate is a database, and Elasticsearch is a search engine. Crate borrows code and functionality from Elasticsearch and other open source components, but adds substantial functionality on top. We have a guide available that explains the differences in more detail.
More resources are always better, from the configuration of a single Crate node as well as the number of nodes in a cluster. For a useful production environment you should have several GB of RAM, 4 cores, and a decent SSD drive per node. We recommend at least a five node cluster if resilience and performance are important.
We explicitly maintain support for Spring, Doctrine and SQLAlchemy. Community supported drivers also support ActiveRecord.
You can find the current version of ElasticSearch Crate is using by visiting SERVER_IP:4200, which lists the version.
Crate is mostly configured via a settings file or when launched on the command line via arguments. You can use Crate’s SQL interface to change certain settings at runtime using the SET / RESET commands. The settings that can be changed are marked with Runtime: yes.