Query Delta Lake files using T-SQL language in Azure Synapse Analytics

Query Delta Lake files using T-SQL language in Azure Synapse Analytics

This article is contributed. See the original author and article here.

Azure Synapse now enables you to query data stored in Apache Delta Lake format. This is one of the top feedback requests and we are happy to announce that this feature is now available in public preview.


In this article you will learn how to run the T-SQL queries on a Delta Lake storage from your Synapse workspace.


 


What is Delta Lake?


Delta Lake is an open-source data format that enables you to update your big data sets with guaranteed ACID transaction behavior. Delta Lake is a layer placed on top of your existing Azure Data Lake data that can be fully managed using Apache Spark APIs available in both Azure Synapse and Azure Databricks.


Delta Lake is one of the most popular updateable big data formats in big data solutions, and frequently used by many data engineers who need to prepare, clean, or update data stored in data lake, or apply machine learning experiments.


 


Why would you query Delta Lake with Azure Synapse?


Azure Synapse provides a serverless endpoint with every Synapse workspace. This provides a few key benefits when it comes to querying Delta Lake data:



  • Easy data sharing between Azure Synapse and Azure Databricks without the need to copy and transform data.

  • Connecting a large ecosystem of reporting and analysis tools with your data stored in Delta Lake format.

  • Favorable pay-per-use consumption model where you don’t need to pre-provision resources. You are paying only for the queries that you are executing.


You can use Azure Synapse and Azure Databricks to prepare and modify your Delta Lake data sets placed in the Azure Data Lake storage. Once your data engineers have prepared the data, your data analysts can create reports using the tools such as Power BI.


Using the serverless query endpoint in Azure Synapse, you can create a relational layer on top of your Delta Lake files that directly references the location where Azure Synapse and Azure Databricks are used to modify data. This way, you can get the real-time analytics on top of the Delta Lake data set without any need to wait for a pipeline to copy and prepare data.


 


JovanPop_0-1622049239155.png


 


Data sharing without copy, load, or transformation of Delta Lake files is the main benefit of serverless SQL pools. The serverless endpoint in Azure Synapse represents a bridge between a reporting/analytics layer where you use Power BI or Azure Analysis Services, and your data stored in Delta Lake format. This enables a variety of tools that work on T-SQL endpoints to access Delta Lake data.


In this solution, every role in your organization that works with big data can use the preferred tools to complete their tasks:



  • Data engineers can keep using the standard tools for data preparation and transformation (for example the notebooks in Azure Synapse or Jupyter environment).

  • Data analysts can keep using their favorite reporting tools such as Power BI to analyze data and present the reports to the end users.


Azure Synapse enables your teams to implement end-to-end solutions on top of Delta Lake files with no friction or a need to change their standard tools.


 


How to query Delta Lake in Azure Synapse?


The serverless endpoint in Azure Synapse (serverless SQL pool) enables you to easily query data stored in Delta Lake format. You just need to provide a URI of the Delta Lake folder to the OPENROWSET function and specify that the format is DELTA. If you have plain parquet files, you can easily convert them to Delta Lake format using Apache Spark.


JovanPop_1-1622049239171.png


 


This query enables you to explore data in your Delta Lake data sets. The OPENROWSET function will automatically determine the columns in Delta Lake format and their data types by inspecting Delta Lake folder.


When you complete exploration, you can create views or external tables on top of your Delta Lake folder. The partitioned views are preferred approach if you have partitioned Delta Lake structure because they can more optimally execute your queries and eliminate the partitions that do not contain the data that should be returned in the queries.


Tools and applications such as Power BI or Azure Analysis Service can read data from these views or external tables, without need to know that the underlying data is stored in Delta Lake format.


Learn more about Delta Lake query capabilities in Synapse documentation.


 


Feedback


This feature is currently in public preview, and the Azure Synapse team is happy to hear your feedback.


Some features such as temporal/time-travel queries, automatic synchronization of Delta Lake tables created in Spark pools, and updates of Delta Lake data are still not available in the public preview. We would be happy to get your feedback related to the new features in this scenario, so you can post your ideas in Azure Feeback site.


 

Azure Marketplace new offers – Volume 142

Azure Marketplace new offers – Volume 142

This article is contributed. See the original author and article here.











We continue to expand the Azure Marketplace ecosystem. For this volume, 63 new offers successfully met the onboarding criteria and went live. See details of the new offers below:









































































































































































































































































Applications


10x SuperCore.png

10x SuperCore: Featuring a modular microservices architecture, 10x’s cloud-native core banking platform reduces client costs and accelerates speed to market. Accelerate product lifecycles and respond to market needs while positioning your bank to pursue new revenue opportunities.


AIP to GP inc approvals_query resolution processes.png

AIP to GP including approvals/query resolution processes: Automate invoice data capture, classify incoming documents with rules-based verification and validation, customize your invoice approval process, and more with Engeneum’s supplier invoice data capture and processing solution for Microsoft Dynamics GP.


Alma Linux 8.png

AlmaLinux 8: ProComputers.com provides this preconfigured image of a minimal installation of AlmaLinux 8. This image contains just enough packages to run on Microsoft Azure, bring up a SSH server, and allow users to log in.


AlmaLinux 8 Server.png

AlmaLinux 8 Server: ProComputers.com provides this preconfigured image of AlmaLinux 8 Server with an auto-extending root filesystem and cloud-init included. This is a minimal AlmaLinux 8 image, mainly used as a common base system on top of which other appliances are built and tested.


AlmaLinux 8 with LVM.png

AlmaLinux 8 with LVM: ProComputers.com provides this preconfigured image of AlmaLinux 8 Server with a logical volume manager (LVM) managed OS disk. The LVM physical and logical volumes, as well as corresponding filesystems, are automatically extended during startup if the OS disk is bigger than the default one.


Appogee Time.png

Appogee Time: An online time tracking, office capacity management, and project time recording solution, Appogee Time integrates seamlessly with Microsoft 365 to enable organizations to manage their hybrid working strategy with one cloud-based platform.


Bedrock on Azure.png

Bedrock on Azure: Trustmarque Solutions’ National Health Service (NHS)-focused data warehouse and analytics platform is designed to align with NHS standards and datasets. The solution is engineered as an easy-to-deploy Microsoft Azure-managed service supporting scale, agility, and advanced customization.


BlueVoyant Azure Sentinel Content Kit.png

BlueVoyant Azure Sentinel Content Kit: The BlueVoyant Azure Sentinel Content Kit provides a Microsoft Azure Sentinel as code approach to deploying security content to your Azure Sentinel instance. Seamlessly deliver detection rules, playbook automations, and other advanced content for Azure Sentinel to your Azure tenant.


Conductor4SQL.png

Conductor4SQL: Conductor4SQL on Microsoft Azure helps organizations with a large Microsoft SQL Server environment to remotely manage and administer SQL Server in a more effective and efficient manner.


Conversational BoT.png

Conversational BoT: Yellow Messenger’s AI-powered platform helps enterprises build rich conversational experiences for customers and employees. Drive digital transformation through a service platform that enables seamless orchestration between bots, applications, and people.


DataMiir Assessment.png

DataMiir Assessment: Blueprint Technologies’ data migration solution, DataMiir, makes migrating legacy data and database code from systems such as Oracle or DB2 to the cloud fast and efficient, increasing the ROI of all your data efforts.


distrBind for MGAs.png

distrBind for MGAs: Realize faster cashflow and simplified reporting with distrBind, an automated back-office solution for insurance distributors. distriBind helps ensure risks are bound within the terms of authorities and automates premium reconciliation and carrier reporting.


Epiq Compliance Connector for Microsoft Teams.png

Epiq Compliance Connector for Microsoft Teams: Epiq’s Compliance Connector for Microsoft Teams builds on existing Microsoft 365 security, compliance, and advanced e-discovery functionalities to provide legal teams the context they need to assess Teams data for relevant and privileged content.


Flotiq.png

Flotiq: Flotiq is an API-first content management platform on Microsoft Azure. The enterprise solution provides an easy way to describe your content, populate your system with large amounts of data, and consume data intelligently.


Fresh Gravity - Clinical Study Build Automation.png

Fresh Gravity – Clinical Study Build Automation: Developed by Fresh Gravity, the Clinical Study Build Automation toolkit leverages natural language processing and machine learning capabilities to effectively generate study build components.


HR employee retention application.png

HR employee retention application: Sii’s employee retention solution uses Microsoft Azure services, Microsoft Power BI, and machine learning to select a group of employees or a single employee who might be at risk of leaving the company soon.


ignio Proxy VM.png

ignio Proxy VM: Digitate’s ignio helps transform your IT strategy and business operations from reactive to proactive, enabling a predict, prescribe, and prevent approach across your organization. Adapt to changes efficiently, evolve digitally, and unleash innovation with ignio.


Intelligent Content Software.png

Intelligent Content Software: Intelligent Content Software enables you to modernize and streamline document workflows with Microsoft 365, improving compliance and reducing cost for many types of documents, including policies and procedures, contracts, research publications, and more.


inventCloud Finance.png

inventCloud Finance: Manage and analyze cloud consumption with inventCloud Finance, a cloud-agnostic solution that delivers visibility into your financial investment, helps you predict consumption, and facilitates the management and identification of resources. This application is available only in Portuguese.


MarketPlace as a Service.png

MarketPlace as a Service: An easy-to-use platform empowering the public to purchase approved social care services, OCC MarketPlace provides information and advice for those seeking social services for adults and children. MarketPlace integrates with OCC’s ContrOCC solution and Social Care Case Management systems.


Prosimo AXI.png

Prosimo AXI: Prosimo AXI enables organizations to provide users with fast and secure experiences across all enterprise applications via a common multi-cloud infrastructure powered by data insights and machine learning models.


RiskIQ Security Intelligence Playbooks (Preview).png

RiskIQ Security Intelligence Playbooks (Preview): RiskIQ has created several Microsoft Azure Sentinel playbooks that pre-package functionality to enrich and add context to incidents on the Azure Sentinel platform. These playbooks can be run individually or configured to run automatically in Azure Sentinel.


Senserva Offer for Azure Sentinel.png

Senserva Offer for Azure Sentinel: Senserva, a cloud security posture management (CSPM) solution for Microsoft Azure Sentinel, simplifies the management of Microsoft Azure Active Directory security risks before they become problems by continually producing priority-based risk assessments.


Strapi for Azure.png

Strapi for Azure: Ntegral provides this preconfigured image of Strapi for Microsoft Azure. Based on a developer-first concept, Strapi is a leading open-source headless content management system (CMS) that is fully customizable.


SurgicalAR.png

SurgicalAR: Intended for surgeons, radiologists, specialists, physicians, trainees, and technologists, SurgicalAR is a 510K FDA-cleared medical software device that uses Microsoft HoloLens 2 for advanced visualization of medical imaging data and other healthcare information.


SyncHPC - Cloud HPC Simulation Platform.png

SyncHPC – Cloud HPC Simulation Platform: SyncHPC is an on-demand high performance computing (HPC) platform for running big computing simulation jobs and projects with any CAE, CFD, FEA, weather forecasting, molecular dynamics, bioinformatics, and seismic analysis applications.


The Asset Guardian (TAG) CMMS-EAM Mobility Suite.png

The Asset Guardian (TAG) CMMS-EAM Mobility Suite: Powered by Microsoft Azure, TAG’s Mobility Suite puts all the power and interconnectivity of TAG CMMS software into the hands of maintenance technicians, maintenance managers, maintenance requestors, and service customers. Streamline operations, boost productivity, and more.


The Train Brain - Bus delay forecasting.png

The Train Brain – Bus delay forecasting: Available for bus services that provide real-time positioning of buses via open data feeds, The Train Brain provides bus delay forecasting to help the public transport sector deliver more accurate traffic information to their customers.


Threat Detection Marketplace.png

Threat Detection Marketplace: Threat Detection Marketplace from SOC Prime helps organizations overcome major stumbling blocks to continuous cyber defense, including lack of custom use cases, limited engineering capacity of the in-house SOC team, shortage of security operations center skills, and more.


Ubuntu 20.04 LTS Desktop as a Service (DaaS).png

Ubuntu 20.04 LTS Desktop as a Service (DaaS): Ntegral provides this preconfigured image of Ubuntu 20.04 LTS desktop as a service (DaaS). Independent from any physical hardware and hosted on Microsoft Azure, the fully managed Ubuntu virtual desktop enables remote work and comes with the tools to help users in their day-to-day activities.


Wavefront Prometheus Adapter Container Image.png

Wavefront Prometheus Adapter Container Image: Bitnami offers this preconfigured Wavefront Prometheus Adapter container image. Wavefront Storage Adapter is a Prometheus integration for transferring metrics from Prometheus to Wavefront. It lets you save Prometheus data in Wavefront without changing your Prometheus setup.


Wavefront Prometheus Adapter Helm Chart.png

Wavefront Prometheus Adapter Helm Chart: Bitnami offers this preconfigured of a Wavefront Prometheus Adapter Helm chart. Wavefront Storage Adapter is a Prometheus integration for transferring metrics from Prometheus to Wavefront. It lets you save Prometheus data in Wavefront without changing your Prometheus setup.



Consulting services


Analyzing Data Pipelines - 2-Day Assessment.png

Analyzing Data Pipelines – 2-Day Assessment: Data is the lifeblood of any business, and ensuring relevant data is captured and stored properly is critical to operationalize it. Atmosera’s team will analyze your existing data pipelines and make recommendations for operational efficiencies with a transition to Microsoft Azure Data Factory.


App Modernization with Cloud4C 10-Day Assessment.png

App Modernization with Cloud4C: 10-Day Assessment: Cloud4C Services offers this free cloud readiness assessment to modernize your organization’s applications and accelerate digital transformation. Optimize your operations and take advantage of elastic resources to dynamically meet changing demand with Microsoft Azure.


Application Innovation diconium 4-Week Workshop.png

Application Innovation: 4-Week Workshop: Learn the basics, concepts, and best practices of cloud-native development; understand the benefits of rapid application prototyping; and get a roadmap for further implementation in this workshop from diconium digital solutions. This service is available only in German.


Apption Data Assessment for AI Readiness 1 Week.png

Apption Data Assessment for AI Readiness: 1 Week: Is your organization ready to take advantage of artificial intelligence and machine learning? Apption will assess your data for launching AI initiatives and show you how you can utilize Microsoft Azure Cognitive Services with your own data.


Apption Data Privacy Assessment 1 Week.png

Apption Data Privacy Assessment: 1 Week: Apption’s Data Privacy Assessment delivers a deep analysis of your Microsoft Azure data for personally identifiable information. Ensure you are managing sensitive data assets effectively and discover new uses to expand your use of Azure data services.


AZ Azure Sentinel 10-Week Implementation.png

AZ Azure Sentinel: 10-Week Implementation: Available only in French, Devoteam Australia’s offer includes the integration of Microsoft Azure Sentinel to help improve the security of your business’s infrastructure.


Azure AD Identity Services 3-Hour Information Session.png

Azure AD Identity Services: 3-Hour Information Session: Learn about the steps to successful identity management in Microsoft Azure Active Directory in this free briefing from Izertis. Available only in Spanish, the briefing explains how you will be guided and accompanied throughout the implementation process.


Azure AD Identity Services 4-Day Evaluation.png

Azure AD Identity Services: 4-Day Evaluation: Available only in Spanish, this Microsoft Azure Active Directory Identification Services evaluation from Izertis is designed for any business that wants to learn about and benefit from the advantages of a modern identity management solution.


Azure Configuration and Security 3-Week Assessment.png

Azure Configuration and Security: 3-Week Assessment: Satalyst’s cloud and security experts will conduct a comprehensive security review of your Microsoft Azure deployment, benchmarking your Azure environment against Microsoft and industry best practice. Satalyst will identify vulnerabilities, insecure settings, and more.


Azure Cosmos DB Analysis - 2-Day Assessment.png

Azure Cosmos DB Analysis – 2-Day Assessment: In this free assessment, Atmosera will assess your current data management environment and provide opportunities for improved platform efficiencies to manage large datasets on Microsoft Azure Cosmos DB.


Azure Envision Briefing 4 Hours.png

Azure Envision Briefing: 4 Hours: Gobi Technologies offers this free value-discovery briefing session on how Microsoft Azure can help your business with infrastructure, data, security, and disaster recovery and business continuity. Learn about the Azure services that best fit your business needs.


Azure Foundation Accelerator 4-Week Implementation.png

Azure Foundation Accelerator: 4-Week Implementation: World Wide Technology’s Microsoft Azure Cloud Foundation Accelerator provides you with the required guidance, best practices, and strategy for the rapid adoption of Azure for production or non-production services.


Azure Modernization Consulting 1-Day Proof of Concept.png

Azure Modernization Consulting: 1-Day Proof of Concept: Node4’s proof of concept is designed for customers who wish to modernize an application using Microsoft Azure-native services without the need for a wholesale redevelopment of the application.


Azure Security Technologies 4-Day Workshop.png

Azure Security Technologies: 4-Day Workshop: This workshop from Segment provides IT security professionals with the knowledge and skills they need to implement security controls, maintain their organization’s security posture, and identify and remediate vulnerabilities.


Azure Sentinel Proof of Concept.png

Azure Sentinel Proof of Concept: This engagement from Bridewell Consulting delivers a business- and outcome-focused proof of concept covering use case requirements, event collection and collation, threat detection, risk analysis, and rapid incident investigation and response in Microsoft Azure Sentinel.


Azure Synapse Analytics Fast Start - 4-Week Proof of Concept.png

Azure Synapse Analytics Fast Start – 4-Week Proof of Concept: In this four-week engagement, Stratum will assess your current infrastructure to better understand your business objectives, deploy a custom Microsoft Azure Synapse Analytics environment for a defined set of workloads, and deliver results to help advance your data-driven journey.


Citrix & Azure for Digital Workspace 1-Hour Briefing.png

Citrix & Azure for Digital Workspace: 1-Hour Briefing: Lutech offers this free briefing to gain an understanding of your organization’s needs and determine the next steps for migrating your Citrix environment to Microsoft Azure.


Cloud governance 5-Day Proof of Concept.png

Cloud Governance: 5-Day Proof of Concept: Available only in Spanish, this Microsoft Azure cloud governance proof of concept from Nubiral will help you determine strategic direction, track performance, allocate resources, and ensure your organizational goals are met without violating compliance obligations.


Devops Shift to Github 8-Week Implementation.png

DevOps Shift to GitHub: 8-Week Implementation: Devoteam will implement and support the migration of your organization’s tools to GitHub in four two-week sprints covering everything from design and implementation to platform enrichment and mentoring support for your teams.


Digital Transformation to Azure - 2-Day Assessment.png

Digital Transformation to Azure – 2-Day Assessment: HyerTek will work with your team to evaluate your organization’s cloud-readiness, define all on-premises technologies that can be migrated to Microsoft Azure, and deliver a roadmap to support the next steps in your digital transformation journey.


DPi30 5-Week Implementation.png

DPi30: 5-Week Implementation: Spyglass’s five-week Data Platform in 30 Days (DPi30) implementation is a streamlined engagement designed to help you create a data platform on Microsoft Azure quickly and efficiently. Deliverables include a fully functional data platform.


DPi30 Analytics Jumpstart 10-Week Implementation.png

DPi30 Analytics Jumpstart: 10-Week Implementation: Spyglass’s Data Platform in 30 Days (DPi30) Analytics Jumpstart implementation is a streamlined engagement designed to help you create a data platform on Microsoft Azure quickly and efficiently. Deliverables include a fully functional data platform, analytics application, documentation, and more.


IoT 8-Week Proof of Concept.png

IoT: 8-Week Proof of Concept: Lixar’s eight-week proof of concept includes the analysis of your environment and provisioning of up to three devices to help you take advantage of Microsoft Azure IoT Central according to your organization’s remote monitoring needs.


Kickstarter Citrix on Azure 5-Day Proof of Concept.png

Kickstarter Citrix on Azure: 5-Day Proof of Concept: Login Consultants’ Citrix on Azure Kickstarter enables your organization to quickly and efficiently evaluate the enterprise-class features and user experience of the Citrix Virtual Apps and Desktops (CVAD) service on Microsoft Azure.


Linux and Open Source Database 5-Week Implementation.png

Linux and Open Source Database: 5-Week Implementation: Cloudfield will help migrate your Linux and open-source database environment to Microsoft Azure, enabling you to increase operational flexibility and scalability of critical tasks to better support your business requirements and improve customer experience.


Lutech Sinergetica Perseo ETRM Suite 1-Hour Briefing.png

Lutech Sinergetica Perseo ETRM Suite: 1-Hour Briefing: This free briefing from Lutech Sinergetica will introduce you to the Perseo Energy Trading Risk Management (ETRM) Suite on Microsoft Azure and identify how your company can benefit from the solution.


Modernize apps on Azure 10-Day Workshop.png

Modernize Apps on Azure: 10-Day Workshop: Learn how your organization can benefit from increased scalability, performance, and security by modernizing applications with Microsoft Azure in this 10-day workshop from DexMach.


Remote Desktop Pro — Windows Virtual Desktop with Citrix - 4-Week Proof of Concept.png

Remote Desktop Pro — Windows Virtual Desktop with Citrix – 4-Week Proof of Concept: Cornerstone.IT’s Remote Desktop Pro offering helps eliminate remote management headaches with a customized implementation of Windows Virtual Desktop on Microsoft Azure with Microsoft 365 applications and Citrix Cloud.


SQL Migration to Azure 4-Week Implementation.png

SQL Migration to Azure: 4-Week Implementation: World Wide Technology’s Microsoft Azure SQL Migration Accelerator is designed to help clients move their data to Azure SQL Database quickly and securely, enabling them to start realizing the benefits of Microsoft Azure.


SQL Migration to the Cloud - 2-Day Assessment.png

SQL Migration to the Cloud – 2-Day Assessment: Atmosera will provide a discovery session to analyze your data estate and create a plan to migrate the estate to Microsoft Azure. Additionally, Atmosera will recommend specific sizing and usage configurations to optimize cost and performance.


XG-Protect on Azure 5-Day Implementation.png

XG-Protect on Azure: 5-Day Implementation: Cloudware’s five-day implementation is designed to help you deploy Sophos XG Firewall on Microsoft Azure to protect your virtual machines and web services from malicious threats and vulnerabilities.



Office Scripts is now generally available

Office Scripts is now generally available

This article is contributed. See the original author and article here.

We are happy to announce that Office Scripts in Excel for the web is now generally available for all eligible users!


 


What is Office Scripts?


Office Scripts is an automation feature-set in Excel for the web that allows users with all levels of programming experience to automate their repetitive workflows.


 









“Office Scripts not only enabled the workbook to be located in the cloud, it also replaced all the pre-existing manual updating and maintenance process. It became 100% fully autonomous!” – Leslie Black, Developer, Analysis Cloud Limited.



 


To get started, use the Action Recorder to record the actions you take in Excel. These actions are then translated into a script that you can run at any time. No programming experience required! Need to modify your scripts? Use the Code Editor! It’s a TypeScript-based editor directly within Excel for the web. Use it to edit your existing scripts or to create new ones using the Office Scripts API.


 


https://www.microsoft.com/en-us/videoplayer/embed/RWEBYs


 


 


Want to run a script on a schedule? Use Power Automate and create a Flow to schedule your Office Script to run at a certain time. Or maybe you want to trigger a script to run based on the creation of a new file in a SharePoint site? Power Automate also allows you to trigger scripts based on events from other applications and services to create cross-application workflows.


 


Office Scripts integration with Power Automate.png


 


 









Since creating the solution and publicizing it internally, I have been asked and have implemented it for another two areas in our Intranet, so they also have an automated process. In total, it saves us around six hours effort per month and consequently we have many colleagues (IT and non-IT) starting to use Office Script to help make their work lives easier.” – Gareth Naylor, Group Wide Architect/Strategist, Uniper



 


What licenses include Office Scripts?


Office Scripts is currently available for all users that have a commercial or EDU license that gives access to the Microsoft 365 office desktop apps (e.g., Office 365 E3 and E5 licenses). If you have an eligible license, you’ll find the Office Scripts feature-set in the Automate tab in the ribbon. Please note that if you do not have the Automate tab in Excel for the web, your admin may have disabled the feature.


 


Learn More


Get started scripting with our numerous sample scripts based on real-world scenarios. These samples cover a wide variety of automated solutions from the fundamentals of the Office Scripts API to how to create cross-workbook and cross-application automated workflows with Power Automate.


 


You can also learn more about Office Scripts from these resources: 



 


Learn from the Office Scripts community:



 


Next steps


Please reach out to us as you try out the Office Scripts feature! Your input is critical to make Office Scripts better.



  • Ask questions on Microsoft Q&A under the ‘office-scripts-dev’ tag if you get stuck or have questions about how to automate a workflow.

  • Have feedback on Office Scripts? Send us a smile or a frown. You can also send us feedback by selecting the Send Feedback button located in the overflow menu of the Code Editor.


 


 

New Connectors Available in ADF: Oracle Cloud Storage; Amazon S3 Compatible Storage

New Connectors Available in ADF: Oracle Cloud Storage; Amazon S3 Compatible Storage

This article is contributed. See the original author and article here.

Azure Data Factory is continuously enriching the connectivity to enable you to easily integrate with diverse data stores. We recently released two new connectors: Oracle Cloud Storage; Amazon S3 Compatible Storage, with which you can seamlessly copy files as is or parsing files with the supported file formats and compression codecs from Oracle Cloud Storage or Amazon S3 Compatible Storage for downstream analysis and consumption. Both of the connectors are supported in copy activity as source. You can now find the Oracle Cloud Storage connector and Amazon S3 Compatible Storage connector from ADF connector gallery as below. 


cloud_storage.png


Learn more from ADF Oracle Cloud Storage connector and Amazon S3 Compatible Storage documentation.  For a full list of data stores that are supported in ADF, see this connector overview article.


 

Selectivity and Estimated Row: Variable

Selectivity and Estimated Row: Variable

This article is contributed. See the original author and article here.

SQL Server does not sniff for variable, it just simply uses the fixed value.


 


I’m going to use AdventureWorks 2019 in this post.


——————–Please run this script—————


use AdventureWorks2019


go


IF exists(select 1 from sys.tables where name=’SalesOrderDetail’ and schema_id=schema_id(‘dbo’))


      drop table SalesOrderDetail


go


select * into SalesOrderDetail from [Sales].[SalesOrderDetail]


go


create statistics iProductID ON SalesOrderDetail(productid) with fullscan


go


dbcc traceon(3604,2363)—trace flag 2363 displays more detail about the selectivity


go


——————–Please run this script—————


 


 


Equality(=): all density


DECLARE @pid INT = 0


SELECT * FROM SalesOrderDetail WHERE ProductID = @pid


Liwei_1-1622081895512.png


 


 


456=‘All density’*card=0.003759399*121317


Liwei_4-1622082278046.png


 


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


———————————-trace flag 2363 output———————————-


Begin selectivity computation


Input tree:


  LogOp_Select


      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)


      ScaOp_Comp x_cmpEq


          ScaOp_Identifier QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID


          ScaOp_Identifier COL: @pid


Plan for computation:


  CSelCalcHistogramComparison(POINT PREDICATE)


Loaded histogram for column QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID from stats with id 3


Selectivity: 0.0037594


Stats collection generated:


  CStCollFilter(ID=2, CARD=456.079)


      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)


End selectivity computation


———————————-trace flag 2363 output———————————-


 


 


Non-Equality(<>): 0.9


 


DECLARE @pid INT = 0


SELECT * FROM SalesOrderDetail WHERE ProductID <> @pid


Liwei_2-1622081895514.png


 


 121317*0.9=109185.3, is rounded down to 109185


 


———————————-trace flag 2363 output———————————-


Begin selectivity computation


Input tree:


  LogOp_Select


      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)


      ScaOp_Comp x_cmpNe


          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductI


          ScaOp_Identifier COL: @productid


Plan for computation:


  CSelCalcFixedFilter (0.9)


Selectivity: 0.9


Stats collection generated:


  CStCollFilter(ID=2, CARD=109185)


      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)


End selectivity computation


———————————-trace flag 2363 output———————————-


 


 


 


Inequality(>,>=,<,<=):0.3


declare @productid int=0


select *From SalesOrderDetail where ProductID>@productid


Liwei_3-1622081895516.png


 


121317*0.3=36395.1, is around down to 36395


Please try >=,< and <=, they all use the same selectivity.


———————————-trace flag 2363 output———————————-


Begin selectivity computation


Input tree:


  LogOp_Select


      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)


      ScaOp_Comp x_cmpGt


          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductID


          ScaOp_Identifier COL: @productid


Plan for computation:


  CSelCalcFixedFilter (0.3)


Selectivity: 0.3


Stats collection generated:


  CStCollFilter(ID=2, CARD=36395.1)


      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)


End selectivity computation


 


———————————-trace flag 2363 output———————————-