Import a Database Table

To import database tables, the SAS/ACCESS product for the database must be licensed and configured for the SAS Workspace Server. You can import data from the following databases:
  • Server databases — SAS Data Set, Aster, DB2, Greenplum, MySQL, Netezza, ODBC, Oracle, PostgreSQL, Salesforce, SAP HANA, SQL Server, Teradata, Vertica
  • Hadoop databases — BigInsights, Cloudera, Cloudera Impala, Pivotal HAWQ, Hortonworks, MapR, Pivotal HD
Note: BigInsights, Cloudera, Hortonworks, MapR, and Pivotal HD all use the SAS/ACCESS Interface to Hadoop, even though they have different menu selections.
To import a database table:
  1. From the Import Data window, select the database name from the list of server or Hadoop databases. These lists include only the data sources for which a SAS/ACCESS product is licensed and configured and that your administrator has granted you the capability to use.
    If you want to import SAS Data Set on a server, see Import a SAS Data Set on a Server for more information.
  2. Specify the connection information. Here are some key points about specifying connection information:
    • The Server field corresponds to the host name for the server. Some databases connect using a data source name instead of the combination of server and port.
    • In the DBMS table names field, you can import multiple tables at the same time. To do this, hold down the Ctrl key while selecting the table names in the Choose Tables window. During the import, an icon in the Status column indicates whether the table was successfully imported, if it failed, or if you chose to cancel. By clicking the link in the Remarks column, you can view additional information, such as log or error messages.
    For connection details for specific databases, see the Database Connection Tips topic.
    Most fields are case sensitive. For example, specifying a value of products in the Database field might not be the same as specifying PRODUCTS. Case sensitivity depends on the database vendor. Furthermore, some databases use schemas. Some databases automatically use the user ID as the schema if a schema is not explicitly specified. Be aware that the User ID and Schema fields can be case sensitive. Check with your database administrator if you are unsure.
  3. Click Browse to select the table to import.
  4. (Optional) Expand Options to indicate additional connection options. Here are some key points about specifying additional connection options:
    • For more information about valid values for the Database options field, see SAS/ACCESS for Relational Databases: Reference. View the Data Set Options topic for the type of database that you are working with (for example, Data Set Options for ODBC).
    • You can use the SAS system options field to specify environment variables such as the following:
      set=SAS_HADOOP_JAR_PATH="/path/to/files"
      The options keyword is submitted with any options that you specify in this field.
  5. (Optional) Review the library and location settings by clicking Advanced. Make any necessary changes.
    You do not have access to the Advanced section if you are importing data from the explorer or designer and do not have the Build Data capability. Instead, you can import the data to a general-purpose area or select Store the table in a private location to prevent other users from accessing the data.
  6. Click OK.
After you have successfully imported a table, the connection information is saved, except for the password. This enables you to import additional tables quickly or to reload the table as needed.
If you want to reload data for an existing table using the same table name, you must do one of the following:
  • Use the same library and output folder that were used when the data for the table was originally imported.
  • Indicate both a different library and a different output folder than when the data for the table was originally imported.
If the table fails to reload, the log might not contain any error information. In this case, the log is most likely displaying information about the last successful action on the table.
Note: If you are importing a single table, then you cannot cancel it. You can choose to cancel if you are importing multiple tables. However, the table that is being processed when the cancel was submitted cannot be canceled and will finish loading.