Important Redshift server configuration commands

After configuring the Amazon Redshift cluster, generally there are a few configurations of the settings can change. The most common one is time zone, because you may want to change the time zone of the cluster to the time zone of the region where you want to set your production environment. Because you might be using some system functions accordingly. But there are more options that you can configure, I just going through some of those options.

SHOW ALL:

SHOW command is used to view the current parameter settings. SHOW ALL command is used to view all the settings that you can configure by using the SET command.

show all;

name	setting
analyze_threshold_percent	10
datestyle	ISO, MDY
extra_float_digits	2
query_group	default
search_path	$user, public
statement_timeout	0
timezone	UTC
wlm_query_slot_count	1

 From SQL client:

analyze_threshold_percent:

Values 0 to 100.

Default value :10.

This comes to use when you want to change the behaviour of how redshift analysis the table while fetching the rows. For example, if a table contains 100,000,000 rows and 9,000,000 rows have changes since the last ANALYZE, then by default the table is skipped because fewer than 10 percent of the rows have changed. To analyze tables when only a small number of rows have changed, set analyze_threshold_percent to an arbitrarily small number. For example, if you set analyze_threshold_percent to 0.01, then a table with 100,000,000 rows will not be skipped if at least 10,000 rows have changed. To analyze all tables even if no rows have changed, set analyze_threshold_percent to 0.

Example:

set analyze_threshold_percent to 5;

set analyze_threshold_percent to 0.1;

set analyze_threshold_percent to 0;

datestyle:

Values: Format specification (ISO, Postgres, SQL, or German), and year/month/day ordering (DMY, MDY, YMD).

Default value: ISO, MDY.

This command is used to set the what kind of date format you may want, you can specify the data format based on your requirement.

Example:

show datestyle;

DateStyle

———–

ISO, MDY (1 row) set datestyle to ‘SQL,DMY’;

describe_field_name_in_uppercase:

Values: off (false), on (true).

Default value: off (false).

To set the column names returned by SELECT statements are uppercase or lowercase. If on, column names are returned in uppercase. If off, column names are returned in lowercase. Amazon Redshift stores column names in lowercase regardless of the setting for describe_field_name_in_uppercase.

Example:

set describe_field_name_in_uppercase to on; show describe_field_name_in_uppercase; DESCRIBE_FIELD_NAME_IN_UPPERCASE

——————————–

On

extra_float_digits:

Values: -15 to 2

Default value: 0.

Sets the number of digits displayed for floating-point values, including float4 and float8. The value is added to the standard number of digits (FLT_DIG or DBL_DIG as appropriate). The value can be set as high as 2, to include partially significant digits; this is especially useful for outputting float data that needs to be restored exactly. Or it can be set negative to suppress unwanted digits.

query_group:

Values: The value can be any character string.

Default value: No default.

This will come into picture when you start workflows kind of administrative things.

This parameter applies a user-defined label to a group of queries that are run during the same session. This label is captured in the query logs and can be used to constrain results from the STL_QUERY and STV_INFLIGHT tables and the SVL_QLOG view. For example, you can apply a separate label to every query that you run to uniquely identify queries without having to look up their IDs.

This parameter does not exist in the server configuration file and must be set at runtime with a SET command. Although you can use a long character string as a label, the label is truncated to 30 characters in the LABEL column of the STL_QUERY table and the SVL_QLOG view (and to 15 characters in STV_INFLIGHT).

Example:

set query_group to ‘Monday’;

SET

select * from category limit 1;

…

…

select query, pid, substring, elapsed, label

from svl_qlog where label =’Monday’

order by query;

query	pid	substring	elapsed	label
789	6084	select * from category limit 1;	65468	Monday
790	6084	select query, trim(label) from …	1260327	Monday
791	6084	select * from svl_qlog where ..	2293547	Monday
792	6084	select count(*) from bigsales;	1.08E+08	Monday

search_path:

Values: ‘$user’, public, schema_names.

Default value: ‘$user’, public.

This command specifies the order in which schemas are searched when an object (such as a table or a function) is referenced by a simple name with no schema component.

Example:

The following example creates the schema ENTERPRISE and sets the search_path to the new schema.

create schema enterprise;

set search_path to enterprise;

show search_path;

search_path

————-

enterprise

(1 row)

statement_timeout:

Values: 0 (turns off limitation), x milliseconds.

Default value:0 (turns off limitation).

This command aborts any statement that takes over the specified number of milliseconds.

The statement_timeout value is the maximum amount of time a query can run before Amazon Redshift terminates it. This time includes planning, queueing in WLM, and execution time. Compare this time to WLM timeout (max_execution_time) and a QMR (query_execution_time), which include only execution time.

Example:

Because the following query takes longer than 1 millisecond, it times out and is cancelled.

set statement_timeout to 1;

select * from listing where listid>5000;

ERROR:  Query (150) cancelled on user’s request

Timezone

Values: time zone.

Default value: UTC.

This command is to set the current session. The time zone can be the offset from Coordinated Universal Time (UTC) or a time zone name.

Example:

To set the time zone for database user, use an ALTER USER … SET statement. The following example sets the time zone for dbuser to New York. The new value persists for the user for all subsequent sessions.

set timezone = ‘America/New_York’;