Add multiple schemas per data source connection

Data Sources

We are able to pull multiple schemas for new Redshift, BigQuery, Snowflake, Athena and PostgreSQL sources. For other data sources you currently can only have one schema per data source connection in Chartio, so you would need to add any additional schemas as separate data sources.

As a workaround, you can create custom tables that pull from tables in your other schema(s). Custom tables allow you to write SQL that Chartio inserts directly into Interactive Mode queries. So, you can create a custom table with the following SQL (replacing schema_name and table_name with relevant information from your database):

select * from schema_name.table_name

For reference, you can run the following command to view all available tables and views in all schemas (MySQL/PostgreSQL):

select * from information_schema.tables

Reflecting Multiple Schemas

If Chartio has multiple schema support for your database, you can reflect them into one data source in Chartio. In order to do that, simply leave the Schema field in the Connection tab blank.

schema-field

Then navigate to the Schema tab and click the Refresh Schema button. Select which schemas you want to reflect into Chartio, and hit Apply.

Now you can view your schemas from one data source within Chartio and easily create charts from tables across schemas.

Multiple Schema Source

How to list all available schemas

Most schemas are defaulted to public unless a specific schema is used. To find out what schema to reflect for your datasource, you can run the following query on your database to list all available schemas. Not all databases have this functionality.

Amazon Redshift

select * from pg_namespace;

DB2

select schemaname from syscat.schemata

MySQL

You can leave the schema blank since for MySQL, physically a schema is synonymous with a database.

Oracle

select USERNAME from SYS.ALL_USERS

PostgreSQL

select nspname from pg_catalog.pg_namespace

Presto

show SCHEMAS [ from catalog ]

Snowflake

show schemas;

SQL Server

select * from sys.schemas

Vertica

select table_schema from v_catalog.tables

Related Data Sources Help Articles

See more