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, 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:
-
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.
-
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.
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.
-
Click
Browse to
select the table to import.
-
(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.
-
(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.
-
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.