# Command Reference

## D**ata Wrapper**

### **CREATE SERVER**

Create new server objects.

#### Syntax

```sql
CREATE SERVER [IF NOT EXISTS] <server_name>
    FOREIGN DATA WRAPPER <foreign_data_wrapper_name>
     WITH ( <option> = <value> [, ... ] )
```

#### Description

A foreign server object represents a named external datasource, along with configuration information required to access the datasource. Server objects are referenced during foreign table creation in order to indicate the backing datasource for the created table.

#### Parameters

| server\_name                 | The name of the foreign server to be created.                                                                                                                                       |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| foreign\_data\_wrapper\_name | <p>The foreign data wrapper to use. Available data wrappers are:</p><p>delimited\_file<em>,</em> parquet\_file, regex<em>\_</em>parsed\_file, odbc (beta), raster\_file (beta).</p> |

**Foreign Server Options for File Data Wrappers (delimited\_file, parquet\_file, and regex\_parsed\_file)**

| STORAGE\_TYPE | Available types are LOCAL\_FILE, AWS\_S3.                                                                                                                                                                                                  |
| ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| BASE\_PATH    | Base file path for the data files or directory.                                                                                                                                                                                            |
| S3\_BUCKET    | S3 bucket containing files. Only applies to AWS\_S3 storage type                                                                                                                                                                           |
| AWS\_REGION   | AWS region where files/objects are stored. Only applies to AWS\_S3 storage type.                                                                                                                                                           |
| S3\_ENDPOINT  | Custom S3 endpoint, which allows data access from S3-compatible services, such as Google Cloud Storage. Similar in function to the [COPY FROM](https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#copy-from) command. |

**Foreign Server Options for ODBC Data Wrapper**

{% hint style="info" %}
ODBC HeavyConnect is currently in beta.
{% endhint %}

| DATA\_SOURCE\_NAME | Data source name (DSN) configured in the odbc.ini file. Only one of `data_source_name` or `connection_string` is required.                                                                                                                                   |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| CONNECTION\_STRING | A set of semicolon-separated `key=value` pairs, which define the connection parameters for an RDMS. For example, `Driver=DriverName;Database=DatabaseName;Servername=HostName;Port=1234`. Only one of `data_source_name` or `connection_string` is required. |

**Examples**

Create a local server that uses the `delimited_file` data wrapper and the file path `/home/my_user/data/csv/`:

```sql
CREATE SERVER my_csv_server FOREIGN DATA WRAPPER delimited_file WITH (
  storage_type = 'LOCAL_FILE', 
  base_path = '/home/my_user/data/csv/'
);
```

Create an AWS Server that uses the `delimited_file` data wrapper with an AWS S3 bucket "fsi-s3-bucket" in the us-west-1 AWS region:

```sql
CREATE SERVER my_aws_server FOREIGN DATA WRAPPER delimited_file WITH (
  storage_type = 'AWS_S3', 
  s3_bucket = 'fsi-s3-bucket', 
  aws_region = 'us-west-1'
);
```

Create an ODBC server using a data source name (DSN):

```sql
CREATE SERVER my_postgres_server FOREIGN DATA WRAPPER odbc WITH (
  data_source_name = 'postgres_db_1'
);
```

Create an ODBC server using a connection string:

```sql
CREATE SERVER my_postgres_server FOREIGN DATA WRAPPER odbc WITH (
  connection_string = 'Driver=PostgreSQL;Database=my_postgres_db;Servername=my_postgres.example.com;Port=1234'
);
```

{% hint style="info" %}
To prevent collisions with available HeavyDB default servers, server names with default\*, system\*, and internal\* (case insensitive) prefixes are not allowed.
{% endhint %}

### **ALTER SERVER**

Update server objects.

#### Syntax

```sql
ALTER SERVER <server_name>
    [ SET (<option> = <value> [, ... ] ) ]
ALTER SERVER <server_name> OWNER TO <new_owner>
ALTER SERVER <server_name> RENAME TO <new_server_name>
ALTER SERVER <server_name> SET FOREIGN DATA WRAPPER <foreign_data_wrapper_name>
```

#### **Description**

Allow users to update properties of existing server objects.

#### Parameters

| Parameter                    | Usage                                                                                  |
| ---------------------------- | -------------------------------------------------------------------------------------- |
| Parameter                    | Usage                                                                                  |
| server\_name                 | The name of the foreign server to be updated.                                          |
| new\_server\_name            | The new name that should be assigned to the foreign server.                            |
| option                       | Clause specific to the option to be updated.                                           |
| new\_owner                   | Username of the new foreign server owner.                                              |
| foreign\_data\_wrapper\_name | The name of the foreign data wrapper that should be associated with the server object. |

#### Example: Update server property

Update the my\_csv\_server object's base path “/home/my\_user/data/csv/” to “/home/my\_user/data/new\_csv/”:

```sql
ALTER SERVER my_csv_server WITH (base_path = '/home/my_user/data/new-csv/');
```

#### Example: Change server name

Change the my\_csv\_server object's name to my\_new\_csv\_server:

```sql
ALTER SERVER my_csv_server RENAME TO my_new_csv_server;
```

### DROP SERVER

Delete an existing server object

#### Syntax

```sql
DROP SERVER [ IF EXISTS ] <server_name>
```

#### Parameters

| Parameter    | Usage                                         |
| ------------ | --------------------------------------------- |
| server\_name | The name of the foreign server to be deleted. |

#### Example

Delete the my\_csv\_server server object:

```sql
DROP SERVER my_csv_server;
```

### SHOW SERVERS

View information about existing server objects.

#### Syntax

```sql
SHOW SERVERS 
    [WHERE <metadata_name> { = | LIKE } <metadata_value>]
```

#### Column Results

| Result        | Definition                                                                                                                 |
| ------------- | -------------------------------------------------------------------------------------------------------------------------- |
| server\_name  | Server name used in the CREATE/ALTER SERVER command.                                                                       |
| owner         | User who created the server object or who has been assigned as the owner of the server object via an ALTER SERVER command. |
| data\_wrapper | Name of data wrapper associated with server object.                                                                        |
| options       | JSON string representation of options used in CREATE/ALTER SERVER command.                                                 |
| created\_at   | Timestamp when the server object was created.                                                                              |

#### Example - View all server objects in the current database

```sql
SHOW SERVERS;
Result:
server_name       | owner | data_wrapper   | options                                                                                | created_at
------------------+-------+----------------+----------------------------------------------------------------------------------------+----------------------
my_csv_server     | joe   | delimited_file | {"storage_type": "LOCAL_FILE", "base_path": "/home/my_user/data/csv/"}                 | 2019-11-15T23:54:35Z
my_parquet_server | admin | parquet_file   | {"storage_type": "AWS_S3", "s3_bucket": "my_parquet_files", "aws_region": "us-east-1"} | 2019-11-15T23:54:35Z
```

#### Example - View all server objects with the delimited\_file data wrapper type

```sql
SHOW SERVERS WHERE data_wrapper = 'delimited_file';
Result:
server_name       | owner | data_wrapper   | options                                                                                | created_at
------------------+-------+----------------+----------------------------------------------------------------------------------------+----------------------
my_csv_server     | joe   | delimited_file | {"storage_type": "LOCAL_FILE", "base_path": "/home/my_user/data/csv/"}                 | 2019-11-15T23:54:35Z
```

#### Example - View all server objects with the name containing “csv”

```sql
SHOW SERVERS WHERE server_name LIKE '%csv%';
Result:
server_name       | owner | data_wrapper   | options                                                                                | created_at
------------------+-------+----------------+----------------------------------------------------------------------------------------+----------------------
my_csv_server     | joe   | delimited_file | {"storage_type": "LOCAL_FILE", "base_path": "/home/my_user/data/csv/"}                 | 2019-11-15T23:54:35Z
```

### CREATE FOREIGN TABLE

Creates the foreign table.

#### Syntax

```sql
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <table_name> (
  { <column_name> <data_type> [NOT NULL] [ENCODING <encoding_spec>] }
  [, ... ]
)
SERVER <server_name>
[ WITH ( <option> = <value> [, ... ] ) ]
```

#### Description

A foreign table acts as a proxy to an external data source for which access details are provided by a server object. A foreign table provides an interface for querying data from an external datasource in a similar way to a local table.

#### Parameters

| Parameter                    | Usage                                                  |
| ---------------------------- | ------------------------------------------------------ |
| table\_name                  | The name of the foreign table to be created.           |
| column\_name                 | The name of the column to be created.                  |
| data\_type                   | Data type of the column to be created.                 |
| NOT NULL                     | Specify that NULL Is not allowed for this column type. |
| ENCODING                     | Specify the encoding of the data type.                 |
| SERVER {custom\_server\_name | default\_local\_delimited                              |

#### Foreign Tables Refresh Options

| Parameter                                         | Usage                                                                                                                                                                                      |
| ------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| ALLOW\_FILE\_ROLL\_OFF                            | Allow the oldest files (based on file path sort order) in a directory of files to be rolled off when a refresh occurs. Only applies when REFRESH\_UPDATE\_TYPE is set to APPEND.           |
| REFRESH\_UPDATE\_TYPE={APPEND \| ALL}             | <p>Specifies what portion of the data is refreshed.</p><p>APPEND specifies that new data is added to existing. ALL specifies that updated data can exist anywhere within the data set.</p> |
| REFRESH\_TIMING\_TYPE={MANUAL \| SCHEDULED}       | Identifies if data refresh activities are manual or automated.                                                                                                                             |
| REFRESH\_START\_DATE\_TIME 'YYYY-MM-DDTHH:MM:SSZ' | Identifies the time to start data refreshes. Used with REFRESH\_TIMING\_TYPE=’SCHEDULED’. Time is specified in UTC time in ISO 8601 format and cannot be in the past.                      |
| REFRESH\_INTERVAL interval\_count{D \| M}         | Used with REFRESH\_TIMING\_TYPE=’SCHEDULED’. Available periods: D(ays), M(onth). To specify 5 days, use ‘5D’.                                                                              |

**Foreign Table Options for File Data Wrappers (delimited\_file, parquet\_file, and regex\_parsed\_file)**

| Parameter             | Usage                                                                                                                                                                                      |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| FILE\_PATH            | Name or path to the file that serves as the datasource for the foreign table. This file path is appended to the BASE\_PATH specified in the foreign server.                                |
| REGEX\_PATH\_FILTER   | See [Globbing, Filtering, and Sorting Parquet and CSV Files](https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#globbing-filtering-and-sorting-parquet-and-csv-files) |
| FILE\_SORT\_ORDER\_BY | See [Globbing, Filtering, and Sorting Parquet and CSV Files](https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#globbing-filtering-and-sorting-parquet-and-csv-files) |
| FILE\_SORT\_REGEX     | See [Globbing, Filtering, and Sorting Parquet and CSV Files](https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#globbing-filtering-and-sorting-parquet-and-csv-files) |

**Additional Foreign Table Options for the delimited\_file Data Wrapper**

| Parameter        | Usage                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| S3\_ACCESS\_TYPE | <p>Used to identify the S3 CSV access type. Supported values are S3\_Direct and S3\_Select. This value is not required if using Parquet data formats.</p><p>The S3\_Direct access type uses HeavyConnects's standard CSV data wrapper and HeavyDB supported</p><p>datatypes.</p><p>Choosing the S3\_Select access type uses the <a href="https://aws.amazon.com/about-aws/whats-new/2018/04/amazon-s3-select-is-now-generally-available/">S3 Select </a>capability which can improve performance. S3 Select does not support GEO and ARRAY types and TEXT types with embedded row delimiters such as newlines. If your data contains these types, use the S3\_Direct access type.</p><p>When using S3\_Select with public buckets, the CREATE USER MAPPING FOR PUBLIC SERVER is required to set credentials but is not required when using public buckets and S3\_Direct.</p> |

All delimited file parser options mentioned in <https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#csv-tsv-import>

**Additional Foreign Table Options for the regex\_parsed\_file Data Wrapper**

| Parameter          | Usage                                                                                                                                                                                                                                                                                                                                                                |
| ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| LINE\_REGEX        | Regular expression that matches an line in the given file. Each capturing group in the regular expression should correspond to a column in the foreign table definition. Capturing groups are expected to match columns in number and order specified in the foreign table definition.                                                                               |
| LINE\_START\_REGEX | Regular expression pattern that should match the start of a new entry in the given file. This option is useful for use cases where entries span multiple lines e.g. when a message contains line breaks. If this option is specified, lines with beginnings that do not match this regular expression are appended to the previous line. This parameter is optional. |

**Foreign Table Options for ODBC Data Wrapper**

{% hint style="info" %}
ODBC HeavyConnect is currently in beta.
{% endhint %}

| Parameter      | Usage                                                                                                                                                       |
| -------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SQL\_SELECT    | SQL statement used to issue a query to an RDMS database.                                                                                                    |
| SQL\_ORDER\_BY | Comma separated list of column names that provide a unique ordering for the result set returned by the SQL statement that is set in the SQL\_SELECT option. |

#### Example - Using the default local file server and default property

Create a foreign table that accesses a local CSV file at /home/my\_user/data/csv/my\_file.csv using this default server object.

```sql
CREATE FOREIGN TABLE my_csv_table (first_name TEXT, last_name TEXT ENCODING NONE) 
  SERVER default_local_delimited 
  WITH (file_path = '/home/my_user/data/csv/my_file.csv');
```

#### Example 2 - Using a custom local file server

Create a foreign table that accesses a local CSV file at path /home/my\_user/data/csv/my\_other\_file.csv using the above my\_csv\_server object.

```sql
CREATE FOREIGN TABLE my_csv_table (first_name TEXT, last_name TEXT ENCODING NONE) 
  SERVER my_csv_server 
  WITH (file_path = 'my_other_file.csv');
```

{% hint style="info" %}
The default "default\_local\_delimited" server object can also be used to accomplish the above. However, if you use a custom server object, you do not have to specify the full path when creating foreign tables.
{% endhint %}

#### Example 3 - Using the custom server and duration property

Create a foreign table that accesses a remote Parquet file named my\_parquet\_file, referenced by my\_parquet\_server object, for which content could change every 2 days.

```sql
CREATE FOREIGN TABLE my_parquet_table (first_name TEXT, last_name TEXT ENCODING NONE) 
  SERVER my_parquet_server 
  WITH (file_path = 'my_parquet_file', refresh_interval = '2D');
```

#### Example 4 - Using S3, custom S3 server, and the S3\_Select option

```sql
CREATE FOREIGN TABLE my_s3_csv_table (first_name TEXT, last_name TEXT ENCODING NONE) 
  SERVER my_aws_server 
  WITH (s3_access_type = 's3_select', 
        file_path = 'my_csv_file', 
        refresh_update_type = 'append', 
        refresh_interval = '2D');
```

{% hint style="info" %}
Use the CREATE USER MAPPING command to set access credentials when using S3. S3\_Select requires set credentials even when using public buckets.
{% endhint %}

**Additional Foreign Table Options for the default\_local\_raster Data Wrapper**

{% hint style="info" %}
Raster HeavyConnect is currently in beta.
{% endhint %}

| Parameter                | Usage                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| RASTER\_TILE\_WIDTH      | Specifies the file/block width by which the raster data should be grouped. If none is specified, this will default to the block size provided by the file.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| RASTER\_TILE\_HEIGHT     | Specifies the file/block height by which the raster data should be grouped. If none is specified, this will default to the block size provided by the file.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| RASTER\_FILTER\_BANDS    | <p>Equivalent to the <code>raster\_import\_bands</code> option used during <a href="https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#raster-import">raster import</a>.</p><p>Allows specification of one or more band names to selectively import; useful in the context of large raster files where not all the bands are relevant. Bands are imported in the order provided, regardless of order in the file. You can rename bands using <code>\<bandname>=\<newname>\[,\<bandname>=\<newname,...>]</code> Names must be those discovered by the <a href="https://docs.heavy.ai/apis-and-interfaces/heavysql#options">detection process</a>, including any suffixes for de-duplication.</p>                                                                                                                                                                                                                                                                                                                    |
| RASTER\_POINT\_TRANSFORM | <p>Equivalent to the <code>raster\_point\_transform</code> option used during <a href="https://docs.heavy.ai/loading-and-exporting-data/command-line/load-data#raster-import">raster import</a>.</p><p>Specifies the processing for floating-point coordinate values: <code>auto</code> - Transform based on raster file type (<code>world</code> for geo, <code>none</code> for non-geo).</p><p><code>none</code> - No affine or world-space conversion. Values will be equivalent to the integer pixel coordinates.</p><p><code>file</code> - File-space affine transform only. Values will be in the file's coordinate system, if any (e.g. geospatial).</p><p><code>world</code> - World-space geospatial transform. Values will be projected to WGS84 lon/lat (if the file has a geospatial SRID).<br><br>Note that this option requires correct column types to be specified when creating the table schema i.e. if <code>raster\_point\_transform='none'</code> then the point columns must be specified with integer types.</p> |

### ALTER FOREIGN TABLE

Update properties of existing foreign tables.

#### Syntax

```sql
ALTER FOREIGN TABLE <table_name>
    RENAME TO <new_table_name>

ALTER FOREIGN TABLE <table_name>
    RENAME COLUMN <column_name> TO <new_column_name>    

ALTER FOREIGN TABLE <table_name>
    [ SET (<option> = <value> [, ... ] ) ]
```

The following table options can be altered:

* ALLOW\_FILE\_ROLL\_OFF
* BUFFER\_SIZE
* REFRESH\_INTERVAL
* REFRESH\_START\_DATE\_TIME
* REFRESH\_TIMING\_TYPE
* REFRESH\_UPDATE\_TYPE
* SQL\_ORDER\_\_\_BY
* SQL\_SELECT

#### Description

Allow users to update properties of existing foreign tables.

#### Parameters

| Parameter         | Usage                                        |
| ----------------- | -------------------------------------------- |
| table\_name       | The name of the foreign table to be updated. |
| new\_table\_name  | The new name of the foreign table.           |
| column\_name      | The column name to be updated.               |
| new\_column\_name | The new name of the column.                  |

#### Example - Change foreign table name

```sql
ALTER FOREIGN TABLE my_csv_table RENAME TO my_new_csv_table;
```

#### Example - Change foreign table column name

```sql
ALTER FOREIGN TABLE my_csv_table RENAME COLUMN first_name TO full_name; 
```

#### Example - Change the foreign table update type

```sql
ALTER FOREIGN TABLE my_csv_table SET (REFRESH_UPDATE_TYPE=‘APPEND’);
```

#### Example - Change the foreign table update schedule

```sql
ALTER FOREIGN TABLE my_csv_table 
  SET (REFRESH_TIMING_TYPE=‘SCHEDULED’, 
       REFRESH_START_DATE_TIME=‘2020-12-20 10:30’, 
       REFRESH_INTERVAL=‘1D’) ;
```

### DROP FOREIGN TABLE

Delete a foreign table.

#### Syntax

```sql
DROP FOREIGN TABLE [ IF EXISTS ] <table_name>;
```

#### Description

Allows users to delete an existing foreign table.

#### Parameters

<table><thead><tr><th width="245">Parameters</th><th>Usage</th></tr></thead><tbody><tr><td>table_name</td><td>The name of the foreign table to be deleted.</td></tr></tbody></table>

#### Example: Delete my\_csv\_table

```sql
DROP FOREIGN TABLE my_csv_table;
```

### **REFRESH FOREIGN TABLES**

Refresh a foreign table.

#### Syntax

```sql
REFRESH FOREIGN TABLES <table_name>, <table_name> [ WITH ( <option> = <value> [, ... ] ) ];
```

#### Description

Allows users to do an on-demand refresh of one or more foreign tables.

#### Options

<table><thead><tr><th width="245">Parameter</th><th>Usage</th></tr></thead><tbody><tr><td>EVICT</td><td>Indicate whether to evict all cached foreign table data while refreshing (TRUE) or not (FALSE).</td></tr></tbody></table>

#### Parameters

<table><thead><tr><th width="245">Parameters</th><th>Usage</th></tr></thead><tbody><tr><td>table_name</td><td>The name of the foreign table(s) to be refreshed.</td></tr></tbody></table>

## **User Mappings**

A user mapping is an object that contains access credentials to an external datasource/foreign server. User mappings provide secure handling of user credentials when access to restricted external datasources or foreign servers is required. User mapping DDL commands can only be executed by super users or owners of referenced foreign servers. The following DDL commands are supported.

### **CREATE USER MAPPING**

Create new user mappings.

#### Syntax

```sql
CREATE USER MAPPING [IF NOT EXISTS] FOR PUBLIC SERVER <server_name>
     WITH ( <option> = <value> [, ... ] )
```

#### Description

Allows users to create new user mappings. See related [AWS documentation](https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys) for information about AWS credentials.

#### Parameters

| Parameter    | Usage                                           |
| ------------ | ----------------------------------------------- |
| server\_name | Name of the server the user mapping applies to. |

#### S3 User Mapping Options

| Parameter          | Usage                                                                                                                                                                                                                                                                                                                                                                                              |
| ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| s3\_access\_key    | AWS access key id for accessing S3 objects (see related [documentation](https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys) for more details). This is provided as an option and only applies to AWS S3 based servers (i.e. delimited\_file\_,\_ parquet\_file\_,\_ or regex\_parsed\_file data wrappers with storage\_type of AWS\_S3).     |
| s3\_secret\_key    | AWS secret access key for accessing S3 objects (see related [documentation](https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys) for more details). This is provided as an option and only applies to AWS S3 based servers (i.e. delimited\_file\_,\_ parquet\_file\_,\_ or regex\_parsed\_file data wrappers with storage\_type of AWS\_S3). |
| s3\_session\_token | AWS session token generated for accessing S3 objects using temporary credentials; see [AWS documentation](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp_use-resources.html) for more information. This option applies only to AWS S3-based servers (for example, delimited\_file\_,\_ parquet\_file\_,\_ or regex\_parsed\_file data wrappers with storage type AWS\_S3).   |

AWS credentials that are configured on the server, such as IAM roles, can be used in place of user mappings. Enable this option with the `allow-s3-server-privileges` server configuration.

**ODBC User Mapping Options**

{% hint style="info" %}
ODBC HeavyConnect is currently in beta.
{% endhint %}

| Parameter          | Usage                                                                                                                                                                                                                                                                             |
| ------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| username           | Username on the RDMS. This option only applies to user mappings that reference an `odbc` foreign server, which uses `data_source_name`.                                                                                                                                           |
| password           | Password credential for the RDMS. This option only applies to user mappings that reference an `odbc` foreign server, which uses `data_source_name`.                                                                                                                               |
| credential\_string | A set of semicolon separated `key=value` pairs, which define the access credential parameters for an RDMS. For example, `Username=username;Password=password`. This option only applies to user mappings that reference an `odbc` foreign server, which uses `connection_string`. |

**Examples**

Set up credentials for users to be able to query a foreign table (see related [documentation](https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys) for more details) that gets data from a previously created AWS S3 backed *my\_parquet\_server*,

```sql
CREATE USER MAPPING FOR PUBLIC SERVER my_parquet_server 
  WITH (aws_access_key_id = '1234', aws_secret_access_key = 'abcd');
```

### **DELETE USER MAPPING**

Delete existing user mappings.

#### Syntax

```sql
DROP USER MAPPING [IF NOT EXISTS] FOR PUBLIC SERVER <server_name>
```

#### Description

Allows users to delete existing user mappings.

#### Parameters

| Parameter    | Usage                                           |
| ------------ | ----------------------------------------------- |
| server\_name | Name of the server the user mapping applies to. |

**Examples**

Delete user mapping that was setup for *my\_parquet\_server.*

```sql
DROP USER MAPPING FOR PUBLIC SERVER my_parquet_server;
```