New Features in Version 6.0
New in This Release
Vector 6.0 contains the following new features.
JSON Support
Users can store, access, and generate JSON data. JSON data must be stored in the database using an existing string column type of CHAR and VARCHAR. For more information, see the chapter “Working with JSON Data” in the SQL Language Guide.
Scalar User-defined Functions (UDFs)
Custom scalar functions can be created for use in queries to extend database functionality. The CREATE FUNCTION statement defines a function and DROP FUNCTION removes it. UDFs are supported for X100 tables only.
Functions can be written in SQL, JavaScript, and Python. The implementation languages (other than SQL) must be specified on the installation configuration parameter udf_languages in config.dat.
For details, see Scalar User-defined Functions, CREATE FUNCTION, and DROP FUNCTION in the SQL Language Guide.
Note: SQL and JavaScript UDFs are a development release. Please work with Actian Support before using such UDFs in a production environment.
Python UDFs are in beta status and disabled by default. Do not use Python UDFs in production until Actian certifies this functionality as production ready. Please contact Actian Support if you are interested in beta testing.
Workload Management
The following features help to manage workload of the database:
• Control the access mode for the database – You can disallow insert, update, delete, truncate, copy, and DDL operations using the GRANT READONLY statement.
• Limit row count – The database privilege QUERY_ROW_LIMIT n can be granted to a role, user, group, or public to limit the number of rows returned in a query. This can be set at the session level using SET ACTUAL MAXROW n.
• Abort queries based on defined limits – The database privilege QUERY_ROW_STEP_LIMIT n can be granted to a role, user, group, or public to specify the number of rows an individual query step is allowed to return before the query is canceled. This can be set at session level using SET MAXROWSTEP n.
For details, see Database Privileges in the SQL Language Guide.
Reverse Strings
The REVERSE() function reverses the order of characters in a string. For details, see String Functions in the SQL Language Guide.
Data at Rest Encryption Enhancements
The following enhancements to data at rest encryption provide better security and performance:
• Database-level encryption - The createdb dbname -encrypt command creates a database in which all columns in all tables will be encrypted and prompts you to define the encryption passphrase. The SQL statements ENABLE PASSPHRASE and DISABLE PASSPHRASE, when used with the passphrase, lock and unlock the encrypted database.
• Application level encryption - New AES encryption functions AES_ENCRYPT_IV and AES_DECRYPT_IV use a random initialization vector (IV) with cipher block chaining (CBC) mode, which protects the data from dictionary attacks. The encryption function encrypts the same value differently each time.
For details, see the chapter “Using Data at Rest Encryption” in the Security Guide and Encryption Functions in the SQL Language Guide.
Data at rest encryption applies to X100 tables only.
The data at rest encryption feature in previous versions of Vector is deprecated.
Wildcards in File Names for COPY VWLOAD
SQL statement COPY VWLOAD supports directory names and wildcards in file names when specifying data files to load. A wildcard is a single asterisk that can be used as follows: *, abc*, *abc, abc*def, *abc*. For details, see COPY VWLOAD in the SQL Language Guide.
Pivot Tables
The PIVOT SQL table reference can be used to create a pivot table, which summarizes and groups data selected from a table. For details, see Pivot Tables in the SQL Language Guide.
External Table Enhancement
When creating an external table that references a CSV file, a header is included by default—OPTIONS=('header'='true')—if a header option is not specified. For more information, see CREATE EXTERNAL TABLE in the SQL Language Guide.
Last modified date: 11/09/2022