Simple ASCII Charting Using SQL
It is very easy to create a simple ASCII chart in Vertica using a SQL analytic function! Example: dbadmin=> SELECT c1 a_date, dbadmin-> count(*) cnt dbadmin-> FROM some_data_2_chart dbadmin-> GROUP BY 1 dbadmin-> ORDER BY 1 DESC; a_date | cnt ————+——— 2019-03-20 | 7600474 2019-03-19 | 4254718 2019-03-18 | 7327122 2019-03-17 | 8274362 2019-03-16 | […]
One on One with Davin Potts: 6. Advantages of In-Database Machine Learning
At the recent Data Day Texas event, I sat down with Davin Potts and had a long conversation about a wide variety of subjects. I divided the conversation into multiple chunks by subject, and have been posting them one chunk at a time. In the first post, we discussed the wide variety of programming languages and tools in […]
Arithmetic Bit-Shifting
A fun way to manipulate bits in Vertica SQL is with the use of arithmetic bit-shifting, which moves the bits in a number either left or right and fills in the new values with 0s. Bit-shifting left is an easy way to multiply by powers of 2, while bit-shifting right divides by powers of 2. […]
Three Takeaways from Barnes-Jewish Hospital Data Disruptors Webcast
Putting data to good use is an area of growing importance across the entire healthcare delivery value chain – from hospitals and health insurers to medical device manufacturers, EHR software vendors, genomic researchers, fitness tracker manufacturers, and more. In fact, according to the 2018 Predictive Analytics in Healthcare report from the Society of Actuaries, 58% […]
Convert an Integer to a Binary
Although there is not a built-in function in Vertica to convert an integer to a binary, you can use the Vertica TO_HEX and HEX_TO_BINARY functions to create your own! Example: dbadmin=> SELECT HEX_TO_BINARY(LPAD(TO_HEX(2 & 0xffffffff), 8, ‘0’))::VARBINARY INT_TO_BINARY; INT_TO_BINARY —————— \000\000\000\002 (1 row) dbadmin=> CREATE OR REPLACE FUNCTION int2bin (input INT) RETURN VARBINARY dbadmin-> AS […]
Workshop: Explore Cloud-Optimized Analytics with Vertica in Eon Mode
Vertica will be offering a 2-day workshop this week, March 13 and 14, in Cambridge, MA, designed to bring Vertica users up-to-speed with Eon Mode. Eon Mode is a recently released alternative to Vertica Enterprise Mode that provides highly elastic compute on the cloud. Register now, or you’ll miss your chance. Vertica in Eon mode […]
One on One with Davin Potts: 5. Vertica, KNIME, Python and the New Uber Created Vertica-Python Interface
At the recent Data Day Texas event, I sat down with Davin Potts and had a long conversation about a wide variety of subjects. I divided the conversation into multiple chunks by subject, and have been posting them one chunk at a time. In the first post, we discussed the wide variety of programming languages and tools in […]
Switch the Sign of a Number
To switch the sign of a number you can multiply it by -1. Example: dbadmin=> SELECT col1, -1 * col1 col1_sign_switch dbadmin-> FROM test; col1 | col1_sign_switch ——+—————— 10 | -10 -10 | 10 3 | -3 -3 | 3 (4 rows) Or you can use the Vertica built-in function NUMERIC_UM to do it for […]
Are These Two String Values Like Each Other?
The Vertica built-in function LIKE is used to determine if two strings are like one another, while the NLIKE function is used to determine if two strings are not like one another. Example: dbadmin=> SELECT LIKE(‘ABCDEF’, ‘ABCDEF’); LIKE —— t (1 row) dbadmin=> SELECT NLIKE(‘ABCDEF’, ‘ABCDEF’); NLIKE ——- f (1 row) You can even include […]
Identify a Table’s Primary Key Columns
The Vertica system table PRIMARY_KEYS lists the columns in a table’s primary key. Example: dbadmin=> \dS primary_keys List of tables Schema | Name | Kind | Description | Comment ———–+————–+——–+————————-+——— v_catalog | primary_keys | system | Primary key information | (1 row) dbadmin=> SELECT column_name, ordinal_position dbadmin-> FROM primary_keys dbadmin-> WHERE table_name = ‘test_table’; column_name […]