Vertica Blog

Vertica Blog

Best Practices

Machine learning robot studying equations and graphs

Announcing Vertica 9.x Training: Predictive Analytics Using Machine Learning

Drea Brandford, Technical Consultant
November 20, 2018

In today’s data-driven world, creating a competitive advantage depends on your ability to transform massive volumes of data into meaningful insights. Companies that use advanced analytics and machine learning are twice as likely to be top quartile financial performers, and three times more likely to execute effective decisions. On November 16, 2018, Micro Focus Software...

Insert Spaces Into a Character String: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 20, 2018

In many SQL relational database you will have to use the RPAD function to insert spaces into a character string. That also works in Vertica. However, for a more robust solution, Vertica provides the built-in function SPACE which returns the specified number of blank spaces. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/SPACE.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/RPAD.htm Have fun!

Find the Version of Vertica that Created a Database: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 19, 2018

You can run the VERSION() function as one method of displaying the current version of Vertica. But what if you want to know the version of Vertica running when you created the current database? For that info you can query the VS_GLOBAL_SETTINGS table. Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/DeterminingYourVersionOfVertica.htm Have fun!

Rejected Data Table Row Number: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 15, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters an empty line while loading...
Database Server Room

Reload Data from a Rejected Data Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 14, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against the rejected data in the...

Derive a Table Column’s Default Value from another Column: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 30, 2018

Jim Knicely authored this tip. You can specify a table column's default value using a DEFAULT expression. To see how, check out this quick tip. When you set a default value, Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. In addition, that...

Default a Column’s Value: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 29, 2018

Jim Knicely authored this tip You can specify a table column's default value using a DEFAULT expression. If the operation omits a value for the column, Vertica evaluates the DEFAULT expression and sets the column on load operations. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/column-constraint.htm Have fun!

Display View Column Data Types: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 25, 2018

Jim Knicely authored this tip. Like table columns, view columns also have a data type. You can display the data types of view columns by querying the VIEW_COLUMNS system table. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_COLUMNS.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEVIEW.htm Have fun!
Database Server Room

Insert Text Into Text: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 24, 2018

Jim Knicely authored this tip. The built-in Vertica INSERT function injects a character string into a specified location in another character string. Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/INSERT.htm Have fun!

Reset Your Session: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 23, 2018

Jim Knicely authored this tip. The SHOW command “shows” run-time parameters for the current session. You can modify one or more of these parameters with the SET command. To quickly reset all of the run-time parameters back to their default values, execute the RESET_SESSION function! Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SHOW.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SET/SETSESSIONRUNTIMECAP.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/RESET_SESSION.htm Have fun!

Conveniently Display Key Projection Info: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 22, 2018

Jim Knicely authored this tip. The Vertica GET_PROJECTION_STATUS function can be called to quickly display key projection facts such as segmentation columns, verified fault tolerance, and statistics status. Examples: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/GET_PROJECTION_STATUS.htm Have fun!

Checking the Validity of a Table Audit: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 18, 2018

Jim Knicely authored this tip. The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table. Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100. First let’s...