Troubleshoot Jobs

Changing the Default Maximum Length for SAS Character Columns

Some directives use a SAS Server (SAS Workspace Server) to read or write tables. By default, the character columns for the input tables to such directives will be expanded to 32K in length. The affected directives are as follows:
  • Transform Data in Hadoop
  • Transpose Data in Hadoop
  • Load Data to LASR
  • Copy Data to Hadoop (when a data set from the SAS Server is selected as the input table)
  • Copy Data from Hadoop (when the SAS Server is selected as the location for the target table)
For best performance, we recommend that you reduce the default length of the character columns for these input tables. Specify a value that is short enough to help performance but long enough to avoid truncating the data in character columns. For example, you could use a global option to reduce the column-length value for all new directive instances. You could use the advanced options for an individual directive to set a column-length value for that directive.
If you want to globally change the Maximum Length for SAS character columns for all new directive instances, go to the Directives page. Select Configuration from the Action menu. Select General Preferences from the Configuration window. Specify the desired length for SAS character columns.
If you want to change the Maximum Length for SAS character columns for a specific directive, go to the Directives page and open an appropriate directive. From the Source Table step, select Advanced Options from the Action menu at upper right. Specify the desired length for SAS character columns.
The only way to change the Maximum Length for SAS character columns for a specific directive is to set the appropriate advanced option for that directive. For example, you cannot change the global default for the Maximum Length for SAS character columns and have that dynamically applied to existing directives. The global option applies to new instances of directives only.

Discover New Columns Added to a Source after Job Execution

When you add columns to a source table, any directives that need to use the new columns need to discover them. To make the new columns visible in a directive, open the Source Table page, click the source table again, and click Next. The new columns will then be available for use in the body of the directive, in a transformation or query, for example.

Hive Limit of 127 Expressions per Table

Due to a limitation in the Hive database, tables can contain a maximum of 127 expressions. When the 128th expression is read, the directive fails and the SAS log receives a message similar to the following:
ERROR: java.sql.SQLException: Error while processing statement: FAILED:
Execution Error, return
       code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
ERROR: Unable to execute Hadoop query.
ERROR: Execute error.
SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.
The Hive limitation applies anytime a table is read as part of a directive. For SAS Data Loader, the error can occur in aggregations, profiles, when viewing results, and when viewing sample data.

Unsupported Hive Data Types and Values

The Hive database in Hadoop identifies table columns by name and data type. To access a column of data, SAS Data Loader first converts the Hadoop column name and data type into its SAS equivalent. When the transformation is complete, SAS Data Loader writes the data into the target table using the original Hadoop column name and data type.
If your target data is incorrectly formatted, then you might have encountered a data type conversion error.
The Hive database in Hadoop supports a Boolean data type. SAS does not support the Boolean data type in Hive at this time. Boolean columns in source tables will not be available for selection in SAS Data Loader.
The Bigint data type in Hive supports integer values larger than those that are currently supported in SAS. Bigint values that exceed +/-9,223,372,036,854,775,807 generate a stack overflow error in SAS.