Use ‘data virtualization’ extension of ‘Azure data Studio’ to create external tables for Oracle

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 the ‘Azure Data Studio’.

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

  1. Connect to your Polybase instance.

clipboard_image_11.png

  1. You are able to install ‘Data Virtualization’ extension after the connection is established.
  2. Click the highlighted icon. Put ‘data virtualization’ in ‘search bar’,  and install it.

clipboard_image_12.png

 

It’s available to use after installation. (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.

clipboard_image_13.png

 

  1. Click ‘Oracle’ database source

clipboard_image_14.png

 

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

clipboard_image_15.png

 

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

clipboard_image_16.png

 

Here is an example

clipboard_image_17.png

 

  1. Check the table you need to export

clipboard_image_18.png

 

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

clipboard_image_19.png

 

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

clipboard_image_20.png

 

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

clipboard_image_21.png

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.