Getting Started: When to use the Intelligence Hub SQLite Database
SQLite in Intelligence Hub:
Intelligence Hub relies on SQLite and includes integrated access to SQLite by default. The SQLite database may be accessed by connections as it is convenient and effective per use case.
SQLite is a lightweight, self-contained, serverless database system. It supports most of the SQL-92 standard and it is ACID compliant, which makes it advantageous in quickly working with relatively simple datasets with low overhead.
Activation and Access:
No activation is necessary for SQLite in Intelligence Hub. It is available by default.
The integrated SQLite database system is accessible through the SQLite Connector. This connector is specific to the integrated SQLite database system and cannot be used to connect to an external SQLite database. However, Intelligence Hub can still access an external SQLite database via the Java Database Connector (JDBC) connector.
SQLite has an additional feature that allows for a special database to be stored specifically in volatile memory instead of being written to disk. This is possible only for the database called “:memory:” which may be configured in the SQLite connection.
When to use the Intelligence Hub SQLite Database:
The integrated SQLite database has many advantages, chief among them being convenience and accessibility. No additional database systems need to be installed and no network or compute resources need to be provisioned before using the integrated SQLite. This makes this database system perfect for ephemeral data store and organization where data is stored temporarily or for relatively short periods of time in relatively small volumes before being exported to more permanent storage. SQLite is also ideal for use case development, training, testing, and troubleshooting.
When to use an External Database System:
As the performance and data requirements become more demanding it may be necessary to consider a dedicated enterprise database system. SQLite does not support distributed deployments. Additionally, any resources used by the integrated SQLite are also consuming resources that would otherwise be accessible to Intelligence Hub. If either becomes resource constrained, their use cases may require separation.
While SQLite tables support a large number of concurrent reads, only one entity may write to a table at one time, and reads and writes may not be performed concurrently. Larger relational database management systems (RDBMSs) are more suited to many concurrent writers to a single table.