Editing Tables

❗️

This Page Is for an Old Version of bit.io

Some of the information may be out of date. If you have questions, please get in touch at [email protected]

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.You can add new columns through 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.You can change the name and data type of columns through 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.You can delete columns 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!


Did this page help you?