You can upload data in CSV format to Metabase and start asking questions about it. This feature is best suited for ad hoc analysis of spreadsheet data. If you have a lot of data, or will need to update or add to that data regularly, we recommend setting up a way to load that data into a database directly, then connecting Metabase to that database.
There are a few things admins need to do to support CSV uploads:
- Connect to a database using a database user account with write access. This way Metabase will be able to store the uploaded data somewhere.
- Select the database and schema you want to store the uploaded data in.
- Add people to a group with unrestricted data access to the upload schema database.
- (Optional) specify a prefix for Metabase to prepend to the uploaded tables.
Databases that support uploads
Connect to a database using a database user account with write access
To upload data to Metabase, an admin will need to connect your Metabase to a database that supports uploads using a database user account that has write access to that database.
You can also upload data to the Sample Database included with Metabase (an H2 database), though we don’t recommend using the Sample Database for any data that you want to keep around.
For more, check out:
Select the database and schema that you want to store the data in
If Metabase is connected to a database using a database user account with write access, Admins can enable uploads by:
- Clicking on the gear icon in the upper right on the home page and navigating to Admin settings > Settings > Uploads.
- Selecting the database Metabase should use to store the data.
When people upload a CSV to a collection, Metabase will:
- Create a table to store that data in the database and schema that the Admin selected to store uploads.
- Create a model that wraps the uploaded table, and save that model to the collection the person uploaded the CSV data to.
Primary key detection and auto-generation
When you upload data, Metabase will try to detect whether the uploaded CSV file includes a column with an incrementing ID. The check is pretty basic: it’ll check for a column named
pk that has integers for each row in the uploaded CSV. Each integer in this column should be unique, so if your CSV includes duplicate ID integers, the column won’t work as an entity key.
If Metabase doesn’t detect a primary key, it will create an ID column for you, including this ID column as the first (left-most) column of the uploaded CSV table.
If you don’t want this autogenerated ID column, you can always remove the column from the model Metabase created. Visit the model, click on the info i icon, then Model details. From the model details page, click the Edit definition button. In the Data section of the query builder, click on the down arrow next to the table, deselect the added ID column, and save your changes.
Add people to a group with unrestricted data access to the upload schema
In order to upload CSVs, a person must be in a group with Unrestricted access to the schema you’ve selected to store your uploaded data. Native query editing isn’t required for uploading. See groups and data permissions.
Specify a prefix for Metabase to prepend to the uploaded tables
Admins can optionally specify a string of text to add in front of the table that Metabase creates to store the uploaded data.
File size limit
CSV files cannot exceed 50 MB in size.
While Metabase limits uploads to 50 MB, the server you use to run your Metabase may impose a lower limit. For example, the default client upload limit for NGINX is 1 MB. So you may need to change your server settings to allow uploads up to 50 MB. People on Metabase Cloud don’t have to worry about this.
If you have a file larger than 200 MB, the workaround here is to:
- Split the data into multiple files.
- Upload those files one by one. Metabase will create a new model for each sheet.
- Consolidate that data by creating a new question or model that joins the data from those constituent models created by each upload.
Deleting models and tables created by uploads
You can archive a model by clicking on the three dots in the upper right and selecting Archive.
For deleting models completely, see Deleting items permanently.
You’ll need to drop that table in the database itself (not in Metabase). For how to delete tables, see your database’s documentation.
Note on uploading data to a MySQL database
For speeding up uploads to a MySQL database, we recommend that you set a
ON. You’ll need to set this
local_infile in MySQL, not Metabase. The command-line format is
local_infile is disabled (set to
OFF), Metabase will automatically fall back to uploading CSVs in a much slower way.
For more context, check out: