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:
-
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.