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’
2. Connect to your Polybase instance.
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.
2. Click ‘Oracle’ database source
3. Specify password to create a master key.(This steps will be skipped if the master key exists)
4. Create a credential and external data source. You can reuse these settings next time.
Here is an example
5. Check the table you need to export
Optional, you can check each table to modify the target schema name, table name and the columns you need to export.
6. I suggest to click the ‘Generate script’ in case the creating external table fails.
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.