This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Error experienced with Impala Driver: [Cloudera][ImpalaODBC] (450) Error when calling the Impala Thrift API ExecuteStatement: SSL_read: error code: 0 when attempting to connect. This error is thrown by the DB driver if a connection is taking too long. It is common for it to be experienced intermittently.
Sometimes you only need to read in specific fields from a database or input. Within Alteryx it is as easy as using a Formula tool, Dynamic Rename and Dynamic input!!
The first step is to bring in the master file and the list of field names you wish to carry through from the Master file.
Next attach a formula tool to the field names list and create a new field with the expression "Keep-" + [FieldNames].
This will rename the fields with “Keep-” appended to the field name.
The next step is to use a dynamic rename tool to “Take Field Names from Right Input Rows”. In the 'New Field Name from Column" select the field which has just been created above.
Use a dynamic select tool to ‘Select via a formula’ and use the expression below to find the field which has ‘Keep-“ appended to the left hand side. This will then only bring through the specific fields from the Master file which you have listed in the field name input.
Using a dynamic rename you can return the fields to their original names through a substring function. This will remove the "Keep-" from the field name.
You now just have the fields you want from the Master input file!
Client Service Representative
Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:
Find more information on our visual query builder below:
Unable to find the dll: "OCI.dll" Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version. This error may also appear if you do not have the drivers installed. One way to troubleshoot this is to find out what drivers you have installed! You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not havethe correct drivers for 32 bit or 64 bit connection dependent upon which one you chose. Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.
This is the third article in the series of renaming, moving, and deleting files using a batch file. This workflow will create a .bat file which you can use in other workflows that use the files you specify in this workflow to delete files.
WARNING: Please be careful using this command as it will do what it says it will do. Test thoroughly before adding to production.
Creating the Batch file workflows:
Tools needed: (1)Directory Input, (2)Select, (3)Formula, (4)Summarize, (5)Output Tools.
Directory Tool (1) –
Browse to the file or files you are looking to change
Select Tool (2) –
Fields you will need are Fullpath, Directory, and File Name
Formula Tool (3) – Output Fields Created: Command and Batch File.
Create a new field, Command (String) – This is the field you want to build your command.
'DEL '+ '"'+[FullPath]+ '"'+ ' ' + + '"'+[FileName]+ '"'
Sample Output - DEL "C:\Users\dmenke\Documents\Sample Data\Command Line Test\$200.xlsx" "$200.xlsx"
Create a new field, Batch File (String) – You will take the Directory field from the Directory Input and add a name of what you want the Batch File to be.
Sample Output - C:\Users\JDoe\Documents\Sample Data\Command Line Test\rename_files.bat
Summarize Tool –
Select the Command field and the action will be to concatenate using a separator as \n.
Select the Batch file and the action will be First.
You can rename the Output fields if you choose as it will change the fields to Concat_Command and First Batch File.
Output Tool (5) –
Browse to save file as any name you want (you will be replacing this name). You will want to use the Save as type: (.csv) and add .bat to end of the name. We want to transform that txt file to a bat file.
In the Delimiters Row in the Options section add \0 as the delimiter.
Select Take/Table Name From Field and choose Change Entire File Path.
Select the First_Batch file (or name you called the field) in the Field Containing File Name or Part of File Name and unselect Keep Field in Output.
Adding Batch File to Your Workflow:
The last step is to add this batch file to the Events tab in a Workflow that you want to move the file or files you have chosen in the .bat file in the Configuration window.
Select Add > Run Command> Run Event When> After Run>
Command: Browse to the bat file and click Ok.
See attached Sample Workflow.
Alteryx has the ability to connect with MIT Kerberos and Active Directory (Kerberos SSPI) based Hadoop implementations. With this feature came the need to tell the driver that you are using Windows Kerberos/Kerberos SSPI and not MIT's version.
As users collaborate, the sharing of workflows that connect to databases can be helpful. The In-DB Connection File type allows for this. A database connection is saved as an .indbc file so it can be packaged with a workflow.
One of the powerful things about the Alteryx Designer is that you can do most things related to your workflow right from within the workflow itself. One such operation is creating a Primary Key for your database table and namely using the Pre-Create SQL and Post- Create SQL options in the Input Data and Output Data tools to do that.
What Are Primary Keys?
A Primary Key uniquely identifies a record in a database table. The value of a unique identifier, among other benefits, is that it improves database performance and allows updates on the records.
Primary Keys can be made up of one or multiple columns in the table in a database table. However, a table can only have one Primary Key. Primary Keys cannot be null and must be unique and therefore assigning a Primary Key at least consists of two steps: setting the column to Not Null and then setting it to be the Primary key. The values in the target column you’re setting as Primary Key must only have unique values otherwise the database will throw an error. If no column in the table is suitable to be a Primary key, you can use the Alteryx Record ID tool to create such a column.
This article will deal with the case of using one column as the Primary Key. Once you know how to create a one-column Primary Key, you can find many articles online explaining how to create multi-column Primary Keys in SQL.
Creating Primary Keys in Alteryx
All of the following examples assume that you know how to connect to your database.
1. Primary Key for a new table:
When creating a new table in Alteryx and then saving it on the database, the easiest way is to save the table first and then “alter” the table using Post-Create SQL to set the Primary Key.
The Pre/Post-Create SQL statements are dependent on the database you’re using. In this Article you’ll find examples for SQL Server and Oracle but you can get more examples online.
For SQL Server - Expression 1a:
ALTER TABLE ExampleTest1
ALTER COLUMN PrimaryK int NOT NULL;
ALTER TABLE ExampleTest1
ADD PRIMARY KEY (PrimaryK);
For Oracle (10+) – Expression 1b:
ALTER TABLE "ExampleTest1"
MODIFY "PrimaryK" NUMBER NOT NULL;
ALTER TABLE "ExampleTest1"
ADD CONSTRAINT Example_pk PRIMARY KEY ("PrimaryK");
As you’d notice, expressions 1a and 1b are slightly different.
The words with all capital letters are SQL keywords. These are the commands that the database understands.
“ExampleTest1” is the name of the table; replace it with the name of your table.
“PrimaryK” is the name of field that you want to make as Primary Key. In this example it was generated using the Record ID tool.
In Oracle, Example_pk is the name of the Constraint. You can set it to whatever you like as long as it doesn’t have spaces or special characters and it is unique in the database. For example you can use ExampleTest1_pk.
The int word in the SQL Server statement and the NUMBER word in the Oracle Statement are the types of the column in SQL Server’s and Oracle’s parlance respectively. If you’re using the Record ID tool similar to the example workflow then you can keep one of these types. Otherwise, you will have to change it to the correct type. You can get more details of the database types here: SQL Server, Oracle.
Note: for Oracle if you have set the Output Data tool option Table/FieldName SQL Style to Quoted (default) then you must use the quotations around the table and field names; otherwise remove them.
You can confirm that the table now has a Primary Key by using an Input Data tool and checking the Visual Query Builder. The Primary Key will show a key sign next to the field name.
2. Primary Key on an existing table:
If you already have a table in the database to which you want to assign a Primary Key, there are two cases:
The table already contains a column that can be assigned as the Primary Key:
The column must only contain unique values and no nulls.
You can check the column type for the field you want to set as Primary Key using the Visual Query Builder.
Use the Input Data tool and fill Expression 1a or 1b in the Pre-Create SQL Code In this case the table exists in the database and you only need to assign the primary key so you can use the Pre-Create SQL code. Since the retrieved data is ignored, it’s advisable that you limit the number of rows retrieved to only the first 10 records using these SQL statements:
For SQL Server: SELECT TOP 10 * FROM ExampleTest2;
For Oracle: SELECT * FROM “ExampleTest2” WHERE ROWNUM <= 10
2. The table does not have a column/columns suitable to be a Primary Key:
In this case, you need to bring the data to Alteryx, append a field that can be a Primary Key, write the data out, and then set the Primary Key.
You can use the Record ID to create the new field for the Primary Key.
You will have to drop (delete) the table and re-write it to make this change. To make sure that you read all the data from the table you must use the Block Until Done tool right before the Output Data tool.
Similar to the first example, we will put Expression 1a or 1b in the Post-Create SQL. The difference here is that Output Data tool Output Option is set to Overwrite Table (drop).
These are the most common cases of creating Primary Keys and you can use the same logic to create more complex Primary Keys or indeed move some of the SQL table maintenance right into your workflow using the Pre-Create/Post-Create SQL options.
Side note: if you noticed in my screenshots, I have short names for the database connections (if not, check them out). These are Aliases – a neat way to refer to you database connections. If you’re not using them you should check this article.
Common Errors Related to Primary Keys
Here are the most common errors that might point to issues with primary keys:
Primary Key required for Update option…
Make sure a primary key is declared on the table.
Violation of PRIMARY KEY constraint 'PK_TEST'. Cannot insert duplicate key in object 'dbo.TEST'. The duplicate key value is…
You are trying to insert a key that already exists. Make sure you are not inserting duplicates. Is the Primary Key column alphanumeric but not case sensitive? For example SQL server is not case sensitive and EhzA and ehza are considered duplicate.
Note: if the key appears multiple times on the input file and an update option is chosen, the same record will be updated multiple times.
In regular tools…Cannot insert explicit value for identity column in table 'TEST‘….
The key is set to auto-increment and Alteryx is trying to insert a value in this column. Deselect primary key column before appending to table and let the database create the value.
Write Data In-DB …. An explicit value for the identity column in table 'Test' can only be specified when a column list is used and IDENTITY_INSERT is ON….
The In-DB tools cannot generate the SQL statement needed to update a table that has a key which is set to auto-increment. Either change the way the key is generated in your table or use the regular tools.
For further information, please contact Alteryx Support and one of us will reach out to you.
You can find the workflows used in this article attached to this post. When you open these workflows you will get errors - that's expected because your connection details are different. You'll need to update the connection details and table/column names before using them. These workflows have been created with Alteryx Designer 10.1 (10.1.7.12188).
Fadi, Henriette, Margarita
For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Tool allows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile: