The ACT SQL prompt accepts a number of ACT-specific commands that you issue by typing a backslash followed by a character or combination of characters and arguments. Do not type a semicolon to conclude these commands. Pressing <Enter> executes the command.
These categories of SQL-prompt commands are described in these tables:
- General Purpose Utility Commands in ACT
- Environment Settings in ACT
- Query Buffer Commands in ACT
- Input/Output-Related Commands in ACT
- Installed Function and Installed File Management Commands in ACT
- Informational Commands in ACT
- Formatting-Related Commands in ACT
General Purpose Utility Commands in ACT Command Description \? Prints help for ACT commands. \c[onnect] <dbname> <user> <host ><port> \c[onnect] <dbname> <user ><host>
\c[onnect] <dbname> <user>
\c[onnect] <dbname>
Changes login credentials and/or connects to a new database. The parameters must be specified in the order shown, with a space before each, and parameters may not be skipped. In other words, if only one parameter is specified, it is understood to be DBNAME; if a second parameter is also specified, it is understood to be USER; and so on. \cd [<dir>] Changes the current working directory. \copyright Shows ACT usage and distribution terms. \h Provides help for SQL commands. \h <SQL_commands>|* Provides help with syntax for the specified SQL command, * for all commands. \g Executes the query. Alternatively, terminate the query with a semicolon (;). \password Changes the password for the current user. \q Quits ACT. \! [<commands>] Executes the command in a shell or starts an interactive shell. Environment Settings in ACT Command Description \info Displays the current environment settings. \pager [on|off] Toggles or sets ACT to use the pager to enable paging through large result sets. \set Displays the current ACT parameter settings. \set <param_name> [<param_value>] Sets the ACT parameter setting <param_name> to the value <param_value param_value>. (For example, “\set fetch-count 500” tells ACT to fetch no more than 500 rows at a time when selecting.) If no parameter value is supplied, displays the current setting for the specified parameter. \timing [on|off] Toggles or sets the display for the timing of commands on or off. Note that the output does not include the I/O time. To view the total query execution time including the output formatting and printing time, run ACT with the UNIX time command (using the ACT command line -c or -f flag).
Query Buffer Commands in ACT Command Description \e [<file>] Edits the query buffer (or file) with an external editor. On most systems, this launches your default text editor. When you save and exit the editor, the edited statement is passed back to ACT for running. \g [<file>] Sends the query buffer to the server (and optionally writes the results to file or | (pipe character)). \p Shows the contents of the query buffer. \r Resets (clears) the query buffer. \w <file> Writes the query buffer to a file. Input/Output-Related Commands in ACT Command Description \echo <string> Writes a string to the query output stream (see\o below). \i <file> Executes SQL commands from a SQL script file. (Runs a SQL script.) \o <file> Redirects all query results to a file or | (pipe character). \o Stops sending results to a file and resumes sending them to the ACT shell. \s [<file>] Displays the command history in Linux (optionally, prints the history to a file specified by< file >) Note that query history includes only the first 2048 characters of each query. Installed Function and Installed File Management Commands in ACT Command Description \dF Lists installed files, SQL-MapReduce functions, and other functions in the current schema. Use a regular expression as an argument to display a subset of the available functions. For example, to view all installed functions in the database, issue: \dF *.*
where the first asterisk means "all schemas" and the second means "all functions and files."
\dF+ Shows details for all installed files, SQL-MapReduce functions, and other functions in the current schema. For each function, the output shows the name, schema, owner, upload time, and MD5 Hash fingerprint of the function. Use a regular expression as an argument to display a subset of the available functions. For Example, type \dF+ *.* to show details for functions and files in all schemas in the database.
\dE [<schema_pattern>]. [<function_pattern>] Shows all the installed Teradata Aster SQL-MapReduce® functions for which the current user has privileges. Use a regular expression as an argument to display a subset of the available functions. Shows function name, schema, owner, function version and creation time. \dE+ [<schema_pattern>]. [<function_pattern>] Shows details about all the installed Teradata Aster SQL-MapReduce® functions for which the current user has privileges. Optionally specify schemas and/or functions to display. You may use a regular expression when specifying the schema and function. If no schema is specified, defaults to the current user’s default schema. For each matched function, ACT outputs following information:
- File Name: As-installed name of the file.This is the name you use to access or manage the file with the install, uninstall, and download commands in Aster Database.
- API Version: Shows the API version used to create the function.
- Runner Kind: The kind of function: java or c.
- Usage Syntax: Shows usage syntax.
- Brief Description: Shows a short description of the function.
- Detailed Description: Shows a long description of the function.
- Input Columns: Shows input columns the function accepts.
- Output Columns: Shows output columns the function returns.
- Author: Shows the author of the function.
- Interfaces Implemented: Row function/Partition function/Multiple inputs/Graphic function.
If the specified pattern matches more than one function, ACT will output the results for each matched function in a list. When running \dE+ in the ACT shell with multiple functions, ACT will page the output.
\install <file> [[<schema>] <file_alias>] Installs the file or SQL-MapReduce function in Aster Database. The file must be available on the file system where ACT is running. Note that the database user running this command must have permission to install files and functions in the specified schema. The install file name and the schema name cannot be the same. An error will result if the names are the same.
You cannot install two files or functions with the same name. If attempting to do this, you must follow these steps:
- remove the existing file or function
- install the new file or function
- grant the appropriate privileges on the file or function.
There is a limit of 238MB on the size of the file to be installed. If you try to install a larger file, you will see an error like:
ERROR: row text exceeds limit of 238MB ...
Note that when installing larger files, the queen may run out of memory. The queen needs available memory of approximately eight times the size of the file to be installed, in order to encode, buffer and copy the file.
\download [[<schema>] <file_alias>] <file> Downloads the specified installed file or function (identified by its <file> or <file_alias>) to the machine where ACT is running. Note that the database user running this command must have permission to download files and functions from the specified schema. \remove [[<schema>] <file_alias>] Removes from the cluster the file or SQL-MapReduce function specified by its <file_alias>. Note that the database user running this command must have permission to remove files and functions from the specified schema. Informational Commands in ACT Command Description \d Lists all tables, indexes and views in the current schema. \d [<pattern>] Describes table(s) or index(es). \dt Lists all tables in the current schema. \dt [<pattern>] Prints the schema, name, type, and owner of a table or tables. To see tables in a custom schema, type\dt < schemaname >.* \dv Lists all views in the current schema. . \dv [<pattern>] Describes view(s). \di Lists all indexes in the current schema. . \di [<pattern>] Describes index(es). \dg Lists groups and roles. \dg [<pattern>] Describes group(s). \du Lists users. \du [<pattern>] Describes user(s). \dn Lists schemas. \dn [<pattern>] Describes schema(s). \l List all databases. \extl host=<hostname> [<option_name>= <option_value>, …] Lists all databases on an external Hadoop systems. \extd host=<hostname> database=<dbname> [<option_name>= <option_value>, …] Lists all tables in a database or describes a table on an external Hadoop system. Tip: ACT uses the schema search path (search_path) for the database user when displaying lists of tables, veiws and indexes. The schema search path defaults to the schema search path for the current user in the database.To set the search_path from ACT, issue the following command:
beehive=> SET session search_path TO <schema>;
Multiple schemas are not suppported. If multiple schemas are listed in the search_path, the first schema listed will be used.
To display the current search_path type:
beehive=> SHOW search_path;
You may also set the search_path on the server.
Alternatively, you can specify the schema to use when issuing commands by following the command with a schema qualified reference. This example shows how to display information on all tables in the schema "myschema": \dt myschema.*
Formatting-Related Commands in ACT Command Description \a Toggles between unaligned and aligned output modes. \f [<string>] Shows or sets the field separator for unaligned query output. \t [on|off] Shows only rows (off by default). \x [on|off] Sets or toggles expanded output mode ON and OFF. With expanded output mode turned on, each record is split into rows, with one row for each value, and each new record is introduced with a text label in the form, ---[ RECORD 37 ]---. This can help make wide tables readable on a small screen, and is very useful if you’re trying to read EXPLAIN output. In expanded mode, the number of rows is not returned at the end of the table. Because of this, when querying a table with no rows, you will simply see the ACT prompt again. AFS-Related Commands in ACT Command Description \afs <afs_command> <command_param> Operates on Aster File Store (AFS). For a list of available commands see Aster File Store Commands. \aport [<afs_port>] Shows and/or sets the AFS port.