Editing Tables
bit.io guesses a schema for your table whenever you upload a file, but you can also manage each table's schema yourself!
Adding a Column
Adding a Column with the UI
You can add new columns through the UI.
Nothing's Happening!
You may need to refresh the page to see changes to your tables' schema.
Adding a Column with SQL
If you prefer, you can use SQL to add columns. Let's say we have a table with one row for each guest at our birthday party, and we want to add a column to store whether or not they like cake. We can use the following SQL command to add a column:
ALTER TABLE "example-user/example-repo"."example-table"
ADD COLUMN "likes cake" boolean;
bit -k <your key> query -q "
ALTER TABLE \"example-user/example-repo\".\"example-table\"
ADD COLUMN \"likes cake\" boolean;"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "ALTER TABLE "example-user/example-repo"."example-table"
ADD COLUMN "likes cake" boolean;"}'
For a list of supported types, see SQL Types.
Bonus: Populating New Columns
We can use UPDATE to populate our newly created column. Say we know all of our guests under the age of 10 like cake, and everyone else doesn't.
UPDATE "ellie-bitio/example"."example" SET "likes cake" = age < 10;
bit -k <your key> query -q "
UPDATE \"ellie-bitio/example\".\"example\"
SET \"likes cake\" = age < 10;"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "
ALTER TABLE "UPDATE "ellie-bitio/example"."example"
SET "likes cake" = age < 10;"}'
In general, we can use any SQL function of the existing columns to fill in our new column.
Editing a Column
Editing a Column with the UI
You can change the name and data type of columns through the UI.
Editing a Column with SQL
Say we have a "likes cake" boolean column, but we decide we really want to know what kind of cake each of our guests enjoys. We can rename the "likes cake" column and change its type to Text with SQL.
Renaming a Column
We can rename a column using theRENAME COLUMN instruction.
ALTER TABLE "example-user/example-repo"."example-table"
RENAME COLUMN "likes cake" TO "favorite cake flavor";
bit -k <your key> query -q "
ALTER TABLE \"example-user/example-repo\".\"example-table\"
RENAME COLUMN \"likes cake\" TO \"favorite cake flavor\";"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "ALTER TABLE "example-user/example-repo"."example-table"
RENAME COLUMN "likes cake" TO "favorite cake flavor";"}'
Changing a Column's Data Type
We can change a column's data type using the ALTER COLUMN and SET DATA TYPE instructions.
ALTER TABLE "example-user/example-repo"."example-table"
ALTER COLUMN "favorite cake flavor" SET DATA TYPE Text;
bit -k <your key> query -q "
ALTER TABLE \"example-user/example-repo\".\"example-table\"
ALTER COLUMN \"favorite cake flavor\" SET DATA TYPE Text;"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "ALTER TABLE "example-user/example-repo"."example-table"
ALTER COLUMN "favorite cake flavor" SET DATA TYPE Text;"}'
Specifying Data Type Conversions
When changing the data type of a column, bit.io will automatically attempt to convert between data types for you. If you want to specify how to compute data of the new type from data of the old type, you can add a USING instruction. Here, every cake flavor will be replaced with a default Vanilla, but you can use any SQL function of the old columns.
ALTER TABLE "example-user/example-repo"."example-table"
ALTER COLUMN "favorite cake flavor" SET DATA TYPE Text
USING 'Vanilla';
bit -k <your key> query -q "
ALTER TABLE \"example-user/example-repo\".\"example-table\"
ALTER COLUMN \"favorite cake flavor\" SET DATA TYPE Text
USING 'Vanilla';"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "ALTER TABLE "example-user/example-repo"."example-table"
ALTER COLUMN "favorite cake flavor" SET DATA TYPE Text
USING \'Vanilla\';"}'
Deleting a Column
Deleting a Column with the UI
You can delete columns with the UI.
Careful!
Deleting a column will remove it from your table permanently!
Deleting a Column with SQL
You can delete columns using the ALTER TABLE and DROP COLUMN instructions.
ALTER TABLE "example-user/example-repo"."example-table"
DROP COLUMN "favorite cake flavor";
bit -k <your key> query -q "
ALTER TABLE \"example-user/example-repo\".\"example-table\"
DROP COLUMN \"favorite cake flavor\";"
API_KEY=<your-api-key>
curl --request POST \
--url https://api.bit.io/api/v1beta/query/ \
--header 'Accept: application/json' \
--header "Authorization: Bearer ${API_KEY}" \
--header 'Content-Type: application/json' \
--data '{"query_string": "ALTER TABLE "example-user/example-repo"."example-table"
DROP COLUMN "favorite cake flavor";"}'
Our birthday party is ruined!
Updated 11 months ago