Schema and tables
New to Xata and confused. I have a dbt project that uses a specific schema name and within that schema will be all my tables. I don't see anything in the UI where I can set the schema name. I also tried to upload csv files that are part of the seed data, it adds additional columns, it renames the columns in the seed files (from id to xata_id), and in one columns it removed the values making it all NULLs.
I might be missing something here but I've never seen a postgres db behave like this. Is this broken?
11 Replies
Hi and welcome! Importing CSV to a schema other than the default is currently only supported from the command line (i.e. with
psql).
You will find the connection string to use with psql in your database's Settings page under "PostgreSQL endpoint". You can then create your custom schema and table(s) in it, and use the \copy command to import CSV \copy myschema.mytable FROM 'my.csv' csv;
Some more information about the issues you encountered:
The UI CSV importer uses Xata's REST API, which only supports the default schema and requires the built-in column xata_id. In case no xata_id is present in your CSV file, the id column is automatically used instead for it. There is a command-line tool in the xata CLI, xata import csv https://xata.io/docs/csv-data/import-data#import-data-with-an-auto-generated-schema which supports importing csv with your own id field, without a xata_id in the csv file, and it autogenerates values for xata_id, keeping your own id field as well. That said, this only works for the xata default schema.
More capabilities will be added to the UI importer down the line to support multiple schemas and avoid the xata_id/id interference.Thanks Kostas, will look into this. I also encountered another error, Xata didn't recognized my date/timestamp column so I manually selected, and it went all red.
Can you share steps to reproduce this issue? What does your timestamp column in the csv look like? Was it detected as text and then you switched it to datetime?
Will get back to you on this asap. It is the public available airbnb dataset but i can make some screenshots to show.
Ok I think this might be a bit too much for Xata to handle 😄 I guess text is the only thing Xata can do for such a rich value and when I changed it to timestampz it actually kept the part I want, but flagged it red anyway.
Just to follow up on your initial reply, would it be possible to create the schemas and tables through an SQL IDE like dbeaver?
If you hover over the column name or type while it is red, it should show a pop up with more information about the error. Typically that would be "Some values could not be interpreted as type: timestamptz" which may occur if some values are incompatible or for some reason cannot be "cast" as datetime
How does the value look like in the actual csv file?
timestamptz expects values in UTC so if there's timezone information in the source data that may not be handled
You can use an IDE to manage your schema and tables yes. We've tested with some (Beekeeper studio, pgAdmin), I am not sure we're tried out dbeaver but it should work.
I just gave dbeaver a quick try and things look smooth with it
This is one of the csv files. Looking at the last two columns, it seems a common date time stamp, but after loading the file in Xata, you get the first screenshot. So Xata adds the name of day, the name of month, the conversion to GMT, and when I change to format to timestampz, it actually shows how it's in the csv
Ah excellent. Thanks for testing it.
Thank you for the sample. Indeed, these timestamps are incompatible with the UI CSV importer which expects RFC3339 format (
2014-01-05T16:12:45.000Z).
There is work planned to make the UI CSV import feature work with pg-native timestamps (like the ones in the sample csv above).Thanks for clarifying this, no problem, I'll go with the SQL IDE route of pre-seeding data and take it from there. Cheers.