Given the fact that Cluvio runs the analytical queries directly on your database or data warehouse, it is not possible to use data that live in different data sources directly within a single report.
Having said that, there are several alternatives that you may use to work around that limitation, here is an overview of the most typical approaches:
1. External tables functionality offered by some databases
BigQuery, Redshift, Athena, and Presto are examples of databases that can be configured with data that live outside of the database and performing joins with the primary data.
Here are links to the details:
- Redshift: https://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum.html
- BigQuery: https://cloud.google.com/bigquery/external-data-sources
- Athena: https://docs.aws.amazon.com/athena/latest/ug/what-is.html
- Presto: https://prestodb.io/
2. Use a data warehouse and ETL to pull data into a single place
This is the most common solution, that can be set up fast and operated much cheaper than only a few years ago. The most typical setups combine one of the cloud analytical databases (Redshift, BigQuery, Snowflake, Vertica or even Postgres or MySql) with an ETL tool like StitchData, Fivetran, ETLeap, Talend. The ETL tool can then in scheduled or real-time way pull data from databases or business services (like Zendesk, Salesforce, ...) into the single data warehouse.
With data living in a single DB (typically with per-source schema), it is easy to create high-performance analytical results that combine data across the sources.
3. Query data directly where they live - typically called federated or distributed querying.
This approach was pioneered by Presto DB (originally created by Facebook), AWS has its own version of Presto running under the name Athena, Starburst has own distribution of Presto as a service with additional connectors.
The set of connectors available differs between the products, here are some links that may be useful:
- PrestoDB connectors: https://prestodb.io/docs/current/connector.html
- Athena connectors: https://docs.aws.amazon.com/athena/latest/ug/athena-prebuilt-data-connectors.html
- Starburst data connectors: https://docs.starburstdata.com/latest/connector.html
4. MySql Databases
For completeness, we mention the slightly confusingly named concept of "Database" in MySQL. MySQL Database is what most other DBs call "schema", and MySQL fully supports joining data between databases living in the same database server/instance, using the database name as a prefix.