Skip to content

Use Azure Data Studio to create external table for Oracle server.

Use T-SQL to create external tables for Oracle is cumbersome and error-prone. You may refer the article if you are interested.

Today I’m going to show you how to use ‘Data Virtualization’ extension of ‘Azure data Studio’ to do it.

You may still run into error , however you can modify the ‘script’ to correct it.

1. Download and install  ‘Azure Data Studio’

https://docs.microsoft.com/en-us/sql/azure-data-studio/download?view=sql-server-ver15

2. Connect to your Polybase instance.

image

3. You are able to install ‘Data Virtualization’ extension after the connection is established.

4. Click the highlighted icon. put ‘data virtualization’ in search bar,  and install it.(You can download it for offline installation)

It’s available to use after installation completes. (current version(1.1.0) supports SQL Server and Oracle)

Here is an example of connecting Oracle data source.

1. Right click the database you’d like to store the external table.

image

2. Click ‘Oracle’ database source

image

3. Specify password to create a master key.(This steps will be skipped if the master key exists)

image

4. Create a credential and external data source. You can reuse these settings next time.

Here is an example

image

5. Check the table you need to export

image

Optional, you can check each table to modify the target schema name, table name and the columns you need to export.

image

6.  I suggest to click the ‘Generate script’ in case the creating external table fails.

image

7. Then click ‘Create’ and wait for the result

Here is an example. In case the creation fails, you can manually run the script to check.

image

3 Comments »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: