Data Stream In Tool
Data Stream In Tool
Use Data Stream In to bring data from Alteryx Designer into the In-DB workflow.
In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, refer to In-Database Overview.
Configure the Tool
Connection Name
Connection Name: Select the dropdown to create a new connection, manage an existing connection, or select a connection from the list of connections that have already been set up.
Manage Connections: Select to create, edit, or delete a connection.
Open File Connection: Select to browse to a saved database connection file.
Creation Mode
Creation Mode: Select the appropriate option for writing the data. Choices include:
Create Temporary Table: Uses a CREATE TABLE statement to create a table that will be removed by the Alteryx engine upon workflow completion and requires CREATE TABLE permissions for all data sources except SQL Server. For SQL Server, a temporary table is created. This is necessary to use the data downstream as most databases don’t allow temporary tables to persist beyond the end of the session. If this option is selected, the Table Name field is disabled and displays “[a unique temporary table name is generated on each run]".
Create New Table: Creates a new table. Will not overwrite an existing table. If an HDFS Avro option is selected, the avro.schema.literal fails at 4000 characters and an error will occur on the table creation. Try reducing the character length of the column names, or selecting fewer columns
Overwrite Table (Drop): Completely drops the existing table and creates a new one.
Table Name
Table Name: Enter the name of the database table to create or update. If Create Temporary Table is selected, the Table Name field is disabled and displays “[a unique temporary table name is generated on each run]".
Advanced Query Options
Use this section to define a Pre-SQL statement to execute before the In-DB execution. This option is compatible with all of the above creation modes. Use the 3-dot menu associated with the Pre-SQL statement field to launch the Configure Pre-SQL Statements window, which includes the SQL Editor. Note that Stored Procedures are not currently supported.
If your workflow includes a Data Stream In tool, this is the best place to put session variables in Pre-SQL. If there is no Data Stream In tool, then the Write Data In-DB tool is a good alternative. However, note that Write Data In-DB runs after any Data Stream In tools, if they exist.
Pre-SQL calls in Data Stream In should be primarily used for calls that need to be done before the creation of the Stream In table
Pre SQL statements are compatible with the following technologies: Bulk Loaders, OCI, ODBC, and OleDB.
Pre-SQL Statement: Define a SQL statement to execute before the In-DB execution.
Note
When the workflow runs, the Results window displays messages to show that the tool is executing SQL statements, along with a label (PreSQL).
All SQL statements are sent directly to the database. Pre-SQL statements do not return data. While SQL can return data, Designer doesn't do anything with it.
If there are multiple streams of In-DB tools, there is no guaranteed order of execution between different streams. The Results window displays the order.
Notes
Note
To use this tool with Oracle, you must have permissions to write to the tempspace assigned to GLOBAL TEMPORARY. Contact your Oracle database administrator.
For more information, go to Connect In-DB Tool.