Tables - Part II

1. Tables - Part II

Earlier, we tried querying the table we created, but we were sort of foiled because the table was empty! But now we’re going to insert some data, so by the end of this video, we’ll be able to cackle with victory and say: “Oh, how the tables have turned.” Anyway, let’s insert some data into our table so we can drop it, undrop it, and more. So let’s insert one row as a test: INSERT INTO TEST_DATABASE.TEST_SCHEMA.TEST_TABLE VALUES (28, 'ha!', True, '2024-01-01', NULL, NULL); For now, we’ll put NULL for the variant and geography types, since those are a little more involved. If we query the table now: SELECT * FROM TEST_DATABASE.TEST_SCHEMA.TEST_TABLE; We see the data we inserted. Let’s drop and undrop our table, as we did in the previous video with databases and schemas. It’s simple – just DROP TABLE followed by the fully qualified table name, which is the table name with the database and schema specified. So: DROP TABLE TEST_DATABASE.TEST_SCHEMA.TEST_TABLE; Now let’s run a SHOW TABLES command, but with “IN” and then the database and schema so we’re not looking at all tables across all of our databases. SHOW TABLES IN TEST_DATABASE.TEST_SCHEMA; We see no tables. Then if we UNDROP the table, we’ll see it’s back! UNDROP TABLE TEST_DATABASE.TEST_SCHEMA.TEST_TABLE; SHOW TABLES IN TEST_DATABASE.TEST_SCHEMA; And there it is. Imagine if we had this power in real life! An angry person who yells “drop dead” at someone, and then sees them really drop dead, could be like: “Wait, I didn’t mean it! Undrop, undrop!” If we just run a plain “SHOW TABLES,” we’ll see all our tables across all databases and schemas. There’s a bunch of great metadata here, including the number of rows and the bytes. We could also get information about tables in our account by querying the TABLE_STORAGE_METRICS view in the ACCOUNT_USAGE schema inside the SNOWFLAKE database (remember, this one is created automatically for observability reasons). SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS; But as you can see, this appears to just be giving us information about the tables that we manually generated, so it differs from SHOW TABLES a bit. If we go back to the SHOW TABLES results, we can see that the ORDER_DETAIL table we created in our video on “Stages and Basic Ingestion” is no joke – it’s got more than 670 million rows. So to finish up this video on tables, I want to take another look at the CREATE TABLE command we used to make that table. Back then, we skimmed over this, but now we’re in a position to understand it. CREATE TABLE frostbyte_tasty_bytes.raw_pos.order_detail ( order_detail_id NUMBER(38,0), order_id NUMBER(38,0), menu_item_id NUMBER(38,0), discount_id VARCHAR(16777216), line_number NUMBER(38,0), quantity NUMBER(5,0), unit_price NUMBER(38,4), price NUMBER(38,4), order_item_discount_amount VARCHAR(16777216) ); If you look carefully at the original code we used to make this table, you’ll notice that it said “CREATE OR REPLACE,” but I’ve dropped the “OR REPLACE” here so we don’t accidentally run this and overwrite our amazing table. So you can see that we created the ORDER_DETAIL table to have nine rows, seven of which were of the data type NUMBER, and two of which were VARCHAR. The two VARCHAR we kept with the default byte size, but with the NUMBER, in a few cases we specified the precision and the scale. So quantity we gave a precision of 5 – meaning it could only have five digits – and a scale of 0, so it won’t have any numbers after the decimal place. UNIT_PRICE we gave a scale of 38, but we permitted four of those digits to come after the decimal place. 670 million rows, nine columns. In my world, that’s a lot. As I said, ORDER_DETAILS is no joke. So there you have it! In this video we created a table using the UI and the CREATE TABLE command, we learned about the six categories of Snowflake data types, we dropped and undropped a table, we learned how to check out some metadata for our tables with the SHOW TABLES command and the SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS view, and we revisited the ORDER_DETAIL table we created in a previous video to see what it was we’d done, now that we have more context. And just to check in on our course progress quickly – We’ve done so much! We’ve learned about virtual warehouses and how to scale them, we’ve learned about stages, databases, and schemas. And now we’ve conquered tables. We’re flying. In no time, we’ll hit the end of the course, and maybe, just maybe, we’ll feel a slight sense of regret that we didn’t take more time to stop and enjoy the journey. Next, on to views!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.