Add multiple schemas per data source connection
We’re 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’d need to add any additional schemas as separate data sources.
If your data source can’t pull in multiple schemas, 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 Visual 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
Reflect multiple schemas
If Chartio supports multiple schemas for your database, you can reflect them into one data source in Chartio—just follow these steps:
- Go to the Connection tab for your data source.
- Select the schema(s) you’d like to add to Chartio from the schema selector then click Save.
- Chartio then redirects you to another screen where you’ll select the schemas and tables to include in Chartio then click Apply. Chartio automatically applies your changes then takes you to the Schema tab, where you can edit your schemas as needed.
Delete schemas
Check out our Manage Schemas page for more information about deleting schemas from Chartio.
How to list all available schemas
Most schemas are defaulted to public unless a specific schema is used. To find out which schema to reflect for your data source, you can run the following query on your data source to list all available schemas; however, not all data sources have this functionality.
Amazon Redshift
select * from pg_namespace;
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