New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Seeking community feedback on SQL Server Import extension #2090

Open
yualan opened this Issue Jul 31, 2018 · 10 comments

Comments

Projects
None yet
10 participants
@yualan
Copy link
Member

yualan commented Jul 31, 2018

As part of Microsoft's Hackathon, the engineering team created an initial prototype extension for the SQL Server Import extension, otherwise known as Import Flat File Wizard.

The SQL Server Import extension can import any .txt or .csv file into a database and convert into a SQL table, and this marks our first import experience in SQL Operations Studio. The beauty of this feature is the user is not overwhelmed with complex configuration options when all they want to do is import a flat file.

Harnessing the power of AI, the Import Wizard uses a Microsoft Research technology known as Program Synthesis Using Examples, or PROSE. PROSE can automatically detect a file's data types, delimiters, column names, and file structure without the user having to explicitly define the configuration. See an end-to-end demo in the GIF below.

image

For most users, SQL Server Import turns into a 5 click experience to go from flat file to SQL table. Let's go into a bit more detail.

Requirement

In order to launch the wizard, make sure you have an active connection. Otherwise, error is thrown.

UI Entry Point

After downloading the SQL Server Import Extension (in a future Insider's Build or on August 22nd), the user has two entry points:

  • Hotkey (Ctrl + i on Windows and Mac)
  • Settings (Bottom-left gear) -> Command Palette -> Type 'import'

We are still experimenting the best entry point. Things we are considering and would appreciate feedback:

  • Right-click option on database in object explorer
  • Default Task widget on Database dashboard. (we are concerned of overriding user's default dashboard)

New Table Details

image

In SSMS, the user can only select a file, table name, and schema. We added Server, which detects only active servers, and Databases, which detects databases in the selected server. Since our current implementation allows the wizard to be opened anywhere and not restricted to a database, we allowed user flexibility to open wizard any time. Let us know if this is an issue.

We don't want to daunt the user of having too many configuration options on first page, but please let us know if we should include additional items. Next is disabled until all fields are filled. Table name is auto-filled, but will throw error on client-side if table name already exists.

Preview Data

image

This page makes a call to our PROSE backend and pops out a preview. The PROSE learning is applied to the first 200 rows, but for now we just display the first 50 rows as a sanity check for the user. User does not do anything except view data.

One thing we are considering is adding override options on this page so that the user can:

  • Change delimiter
  • Skip n number of lines of header
  • Change number of lines to apply PROSE learning
  • Change character encoding

The idea is that the user can automatically generate a new preview with new override options. Let us know if this is important to you.

Modify Columns

image

This page allows the user to change Column Names, Data Types, Primary Key, and Nullability. PROSE automatically can learn the data types of the file and will choose best option. As a user, you have an editable dropdown to choose from T-SQL valid data types, and also change number of characters such as varchar(999) in case you have a long string value on row 9999. Let us know if this UI is helpful

We have heard feedback that a clusterable and columnstore checkbox would be helpful when dealing with those server instances. Let us know how important that is to you.

Summary

image

Once you click Next, PROSE will import the whole file into a SQL table that will be added to your database. If it is successful, a success message will appear and tell you how many rows were successfully inserted. Let us know if you would like a bigger checkmark or more colorful indicator.

The more interesting scenario is for error cases. For now, we are working on an error message that says "error at lines 10-20," but due to limitations of SQL Bulk Copy, we can't get any more granular than that. Let us know how high of a priority it is to have of error scenarios you have run into the past so that we can figure out how to best share this information.

Import New file

This is a nice button on the summary page, but this will remember your server and database you selected and right away you can import another file. Makes it easy to import multiple files. We would eventually like to import directories of files or make this scriptable, but there are current complications and bandwidth issues, so that is lower priority for now.

Future work

We captured some additional scenarios in the walkthrough above, but there are some plans in the works:

  • JSON support and additional file support
  • Excel Flash Fill in Modify Columns (so user doesn't have to modify 100 individual columns)
  • Ability to apply learning to a file

We can't wait to get this extension in your hands this month, but please let us know how we can improve the UI for this wizard. Let us know what you prioritize, and we can work together to incorporate into our plans. Please spread the word about this conversation.

Let us know what you would like to see in SQL Server Import in this issue. We would love to hear your feedback as we continue to experiment with Open Design so that we are building features that our users love. Thank you.

@peterlukerow

This comment has been minimized.

Copy link

peterlukerow commented Jul 31, 2018

Definitely there should be an option in the context menu on a database.

Everything on some dashboard is the thing I hate most about SOS. If I want to import a flat file into a database I assert I'm not going to pick a random database I'm going to know exactly which one.

Given SOS seems to be ripping up the rule book, so to speak of how SSMS works at least give config options for the feature so that you can choose to have it on the context menu on a database.

I want to use SOS mainly because it properly implements a dark theme, and some of it's functionality is nicer but it is missing so much functionality and changing so much of the way things use to work there needs to be a little give and take, i.e. have settings so long time SSMS users can have things closer to how they use to be as well as having access to the new bits. Especially important now that MS is seemingly dumping SSMS (a bit like their actions imply on full fat .NET)

@dzsquared

This comment has been minimized.

Copy link

dzsquared commented Jul 31, 2018

Is it on the roadmap to extend the functionality to include inserting rows into an existing table? I know this isn't part of the "Import Flat File" context item in SSMS, but rather the "Import Data" option - but the PROSE API might be helpful in automapping columns based on data types.

@kburtram kburtram added the task label Jul 31, 2018

@kburtram kburtram added this to the Backlog milestone Jul 31, 2018

@yualan

This comment has been minimized.

Copy link
Member

yualan commented Jul 31, 2018

@peterlukerow Sounds good, context menu on a database when extension is downloaded seems to have been requested several times. Would you expect the server and database dropdown in the wizard to be disabled so that the user is only working in that database context? Or should user have flexibility still to change to another database?

Feedback noted. There is a difference between Server dashboards and Database dashboards, so the user should know the context they are in. Our main concern was the default dashboard is entirely up to the user, so overriding a user's default dashboard with a Task widget may not be ideal. Open to feedback. We can also look into adding better config settings.

To be transparent, SSMS is not going away. @vickyharp went into more detail about our roadmap here, but in summary, we are continuing investments in SSMS through our monthly releases and consider SSMS as our flagship tool. On SQL Ops side, we will continue to make investments in bringing over the most customer requested features like SQL Agent and Profiler into SQL Operations Studio. The upside with SQL Operations Studio is that we have an opportunity to work with this talented community to revisit some of the limitations in UI of SSMS and see if we can improve it or keep things the same.

We have made some efforts to bring over things like SSMS keymap, these extensions, and Edit Data, but these all came from the community asking us to bring it in. We love to see this passion for SQL Server of what you like and what you don't like, and this feedback is valuable to us to build great tools.

@dzsquared
Yes, this is currently on the roadmap and has been requested for the Import Flat File Wizard currently in SSMS. Do you have a suggestion of what would be a good flow in your mind to expose this functionality without having too many configuration options? For others, please chime in if this is something you are looking for by liking dzsquared's comment because if this is a very common scenario, we can move it up in priority.

@DaveDustin

This comment has been minimized.

Copy link

DaveDustin commented Aug 1, 2018

Similar to what @dzsquared said, we'd like to see the ability to import content into an existing object if present, or create one, even if a match is found.

Change the order of operations

  • You open a file to import, it does it's analysis determining columns, types etc..., allows the user to change types.
  • Next choose where to store the data, but have the application scan the destination database to see if any existing objects look like the proposed schema. If they do, offer the option to import into that table (with optional delete of existing content first)

@kburtram kburtram referenced this issue Aug 1, 2018

Closed

csv import #785

@peterlukerow

This comment has been minimized.

Copy link

peterlukerow commented Aug 6, 2018

@yualan - I wouldn't mind if the wizard disabled server/DB selections or left it open to change really. I'd lean towards disabled to enforce the route taken to get to that point.

Regarding MS commitment to SSMS, sorry that's not reassuring. Every time there is a new release of SSMS that are lots of comments literally saying things like "Dark theme 5000+" over and over across the last couple of years. Despite the fact that you can tweak the config file to enable dark theme that works 90% MS' only response is - "it's not currently on our roadmap". On the other hand SOS has dark theme out the box but lacks the functionality. However the functionality is gaining with each release, unlike Visual Studio vs. VS Code when feature parity hits it seems like there will no point in having both.

Regarding .NET full fat vs. .NET core it's a similar story. MVC on full fat .NET hasn't seen an update in 3 years, for example, mean whilst every article about .NET is about some new core thing that isn't available or isn't parity in full fat.

@rdymade

This comment has been minimized.

Copy link

rdymade commented Aug 28, 2018

I want to test this feature also but i am unable to find it. I downloaded the August Preview Insiders Build 0.32.6 from the 25.08.2018. Ctrl + i and the entry via Command Palette are not there.
Am i missing something?

I'm also interested in the import into an existing table.

UPDATE:
I downloaded the source (the feature branch) and build it myself now i see the extension but i get the error unsupported linux distribution.

Error happens in sqlopsstudio/node_modules/zone.js/dist/zone-node.js:2280

OS: Arch Linux (updated today)
NodeJS: v8.11.4

@ndwivedi

This comment has been minimized.

Copy link

ndwivedi commented Aug 31, 2018

I do not see Import flat file extension in market place. Not sure what I am doing wrong. I installed latest version of Operations studio.

image

@chojrak11

This comment has been minimized.

Copy link

chojrak11 commented Nov 12, 2018

I'd like to be able to use this kind of feature from command line. That's crucial.

@abusk1

This comment has been minimized.

Copy link

abusk1 commented Nov 25, 2018

Hello @yualan

I'm a beginner at SQL.
I try to import a csv file using the Import Flat File Wizard, but at the end of the wizard I get the error: "Error inserting data into table".
Whats wrong?

best regards

@dzsquared

This comment has been minimized.

Copy link

dzsquared commented Nov 25, 2018

Hello @yualan

I'm a beginner at SQL.
I try to import a csv file using the Import Flat File Wizard, but at the end of the wizard I get the error: "Error inserting data into table".
Whats wrong?

best regards

@abusk1 In using this extension frequently, I find that I get that error message whenever the data types selected by the wizard based on the first 50 rows of the csv don't match all the way through and there's a data type mismatch. (for example, an int column eventually has non-numeric data or a not null column is empty further down)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment