Introducing U-SQL database projects – U-SQL database development and deployment made easy (public preview)

Today we are pleased to introduce the U-SQL database project, a new project type in Azure Data Lake Tools for Visual Studio (ADL Tools for VS) that accelerates U-SQL database development, management and deployment.

All objects (except for credentials) in U-SQL databases can be created and managed with the U-SQL Data Definition Language (DDL). Before, some customers used inline DDL statements in a U-SQL query to manage database object definitions. But inline DDL statements drop and recreate objects every time a script is run, which not only causes redundant metadata operations, but can also bring job failures due to metadata locking issues. Some customers use a general U-SQL project to manage the database definitions using multiple U-SQL scripts. This is more organized and manageable but when deploying the database, they need to take care of the order of script submissions and manage object dependencies. Furthermore, they need to take extra steps to track deployment job status in order to guarantee atomic deployment.

To address these inconveniences, ADL Tools for VS (after version 2.3.4000.3) provides the U-SQL database project to help customers develop, manage and deploy U-SQL databases.

Key benefits

Simplifies the U-SQL database development, management and deployment experience

A U-SQL database project is the abstract description for a U-SQL database. Customers can manage all DDL statements, assembly sources and dependent resources in this U-SQL database project. The build output of the U-SQL database project is a database deployment package (.usqldbpack), which can be deployed to an ADLA account to create a U-SQL database instance.

Allows management of database environment for local build and run

If a U-SQL script queries tables and references assemblies, the database environment needs to be set up locally before build and local run, or it will fail due to missing database references. With the U-SQL database project, customers can now manage database dependencies by adding database project references to the U-SQL project.

After adding database references, the build process automatically deploys the database environment before compiling U-SQL scripts, allowing projects to build without any additional database setup tasks. Learn more about database project reference.

You can also run U-SQL queries against the Local-project account without deploying and refreshing database environment, ADL Tools for VS helps to set up referenced database environment before local execution. Learn more about local run with (Local-project) account.

Try it now

Start using the U-SQL database project for U-SQL database development. Kindly let us know your feedback at adldevtool@microsoft.com.

Learn how to use U-SQL database project to develop U-SQL database for Azure Data Lake.

Known limitation for public preview

DROP statement is now allowed during public preview, we will add allowing drop and allowing data-related drop configurations in the next release.

Comments

• Anonymous
  October 02, 2018
  When do you anticipate the next release to be made available? Anxiously wanting the DROP statement support.
  • Anonymous
    October 07, 2018
    The next release is available now. You can get it from here: http://aka.ms/adltoolsvs.
    • Anonymous
      October 10, 2018
      Yanan, thank you for replying. I currently have version 2.3.5001.1 of ADL Tools for Visual Studio (2017) installed. However if I uncomment a "DROP PROCEDURE IF EXISTS..." statement at the top of my CREATE PROCEDURE script still causes an error:Error [DBProject]Drop object DDL statement is not enabled Any estimate on the DROP support?