Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1052186%22%20slang%3D%22en-US%22%3EUse%20%E2%80%98Data%20Virtualization%E2%80%99%20extension%20of%20%E2%80%98Azure%20data%20Studio%E2%80%99%20to%20create%20external%20tables%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1052186%22%20slang%3D%22en-US%22%3E%3CP%3EUse%20T-SQL%20to%20create%20external%20tables%20for%20Oracle%20is%20cumbersome%20and%20error-prone.%20You%20may%20refer%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FSQL-Server-Support%2FAn-example-of-Polybase-for-Oracle%2Fba-p%2F1044636%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Earticle%3C%2FA%3E%20if%20you%20are%20interested.%3C%2FP%3E%0A%3CP%3EToday%20I%E2%80%99m%20going%20to%20show%20you%20how%20to%20use%20%E2%80%98data%20virtualization%E2%80%99%20extension%20of%20%E2%80%98Azure%20data%20Studio%E2%80%99%20to%20do%20it.%3C%2FP%3E%0A%3CP%3EYou%20may%20still%20run%20into%20error%20%2C%20however%20you%20can%20modify%20the%20%E2%80%98script%E2%80%99%20to%20correct%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EDownload%20and%20install%20the%26nbsp%3B%E2%80%98Azure%20Data%20Studio%E2%80%99.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fazure-data-studio%2Fdownload%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fazure-data-studio%2Fdownload%3Fview%3Dsql-server-ver15%3C%2FA%3E%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EConnect%20to%20your%20Polybase%20instance.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160359iBE6305DF50402EFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_11.png%22%20title%3D%22clipboard_image_11.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3EYou%20are%20able%20to%20install%20%E2%80%98Data%20Virtualization%E2%80%99%20extension%20after%20the%20connection%20is%20established.%3C%2FLI%3E%0A%3CLI%3EClick%20the%20highlighted%26nbsp%3Bicon.%20Put%20%E2%80%98data%20virtualization%E2%80%99%20in%20%E2%80%98search%20bar%E2%80%99%2C%26nbsp%3B%20and%20install%20it.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160357i5CD9BC913BC996A2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_12.png%22%20title%3D%22clipboard_image_12.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%E2%80%99s%20available%20to%20use%20after%20installation.%20(Current%20version%20(1.1.0)%20supports%20SQL%20Server%20and%20Oracle)%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%20of%20connecting%20Oracle%20data%20source.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ERight%20click%20the%20database%20you%E2%80%99d%20like%20to%20store%20the%20external%20table.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160358i80894899E93AD274%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_13.png%22%20title%3D%22clipboard_image_13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EClick%20%E2%80%98Oracle%E2%80%99%20database%20source%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160360i75D52FA30206BEFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_14.png%22%20title%3D%22clipboard_image_14.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3ESpecify%20password%20to%20create%20a%20master%20key.%20(This%20steps%20will%20be%20skipped%20if%20the%20master%20key%20exists)%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160362iA7EAF5EDA55D9A06%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_15.png%22%20title%3D%22clipboard_image_15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%224%22%3E%0A%3CLI%3ECreate%20a%20credential%20and%20external%20data%20source.%20You%20can%20reuse%20these%20settings%20next%20time.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160361i4D4213100A84DA22%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_16.png%22%20title%3D%22clipboard_image_16.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160363i04064C097B5CA4DC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_17.png%22%20title%3D%22clipboard_image_17.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%225%22%3E%0A%3CLI%3ECheck%20the%20table%20you%20need%20to%20export%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160364i582269C9563B4F4B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_18.png%22%20title%3D%22clipboard_image_18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOptional%2C%20you%20can%20check%20each%20table%20to%20modify%20the%20target%20schema%20name%2C%20table%20name%20and%20the%20columns%20you%20need%20to%20export.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160365i267AF0AE0CFB824F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_19.png%22%20title%3D%22clipboard_image_19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%226%22%3E%0A%3CLI%3EI%20suggest%20to%20click%20the%20%E2%80%98Generate%20script%E2%80%99%20in%20case%20the%20creating%20external%20table%20fails.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160366iE0A292594C39A4BF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_20.png%22%20title%3D%22clipboard_image_20.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%227%22%3E%0A%3CLI%3EThen%20click%20%E2%80%98Create%E2%80%99%20and%20wait%20for%20the%20result%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EHere%20is%20an%20example.%20In%20case%20the%20creation%20fails%2C%20you%20can%20manually%20run%20the%20script%20to%20check.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160367i6521FFDEE2DDA451%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_21.png%22%20title%3D%22clipboard_image_21.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1052186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160368iF364B0E8FC6E060A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20title%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1052186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%202019%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067132%22%20slang%3D%22de-DE%22%3ESubject%3A%20Use%20'Data%20Virtualization'%20extension%20of%20'Azure%20data%20Studio'%20to%20create%20external%20tables%20for%20Or%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067132%22%20slang%3D%22de-DE%22%3E%3CP%3EA%20short%20question%2C%20is%20it%20a%20bug%2C%20that%20you%20cannot%20adjust%20the%20rows%20imported%20as%20mentioned%20in%20point%205%20optionally%3F%20I%20am%20struggling%20with%20oracle%20tables%20not%20being%20able%20to%20import%20because%20of%20RAW%20columns.%3C%2FP%3E%3CP%3EThe%20importer%20simply%20tells%20me%20that%20the%20import%20is%20not%20possible.%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EThomas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1067543%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20%E2%80%98Data%20Virtualization%E2%80%99%20extension%20of%20%E2%80%98Azure%20data%20Studio%E2%80%99%20to%20create%20external%20tables%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1067543%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Thomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20your%20comment.%20Can%20you%20provide%20more%20detail%20about%20'%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23f8f8f8%3B%20color%3A%20%23333333%3B%20font-family%3A%20'SegoeUI'%2C'Lato'%2C'Helvetica%20Neue'%2CHelvetica%2CArial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3Eyou%20cannot%20adjust%20the%20rows%20imported%20as%20mentioned%20in%20point%205%20optionally%3C%2FSPAN%3E'.%26nbsp%3B%20Do%20you%20use%20'Data%20virtualization'%20or%20'T-SQL'%2C%20how%20did%20you%20want%20to%20adjust%20the%20rows%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

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

 

2 Comments
Occasional Visitor

A short question, is it a bug, that you cannot adjust the rows imported as mentioned in point 5 optionally? I am struggling with oracle tables not being able to import because of RAW columns.

The importer simply tells me, that the import is not possible.

Best regards,

Thomas

Microsoft

Hi Thomas,

 

Thanks for your comment. Can you provide more detail about 'you cannot adjust the rows imported as mentioned in point 5 optionally'.  Do you use 'Data virtualization' or 'T-SQL', how did you want to adjust the rows?