Questions tagged [u-sql]

1

votes
1

answer
160

Views

Unable to execute R code on U-SQL using R extensions

I have been trying to execute R code on U-SQL using the R extensions mentioned in the documentation (https://docs.microsoft.com/en-us/azure/data-lake-analytics/data-lake-analytics-u-sql-r-extensions). When I try to execute the example scripts mentioned in the link above, it throws the error: C# err...
Absolute Beginner
1

votes
0

answer
61

Views

U-SQL Python Reducer __init__.py already exists error

The first time I run a U-SQL script containing a Python reducer, it runs successfully: 'Completed with 'Success' : 2/9/2018 3:06:12 PM' The second time I run a U-SQL script containing a Python Reducer I receive this error: 'An unhandled exception from user code has been reported when invoking the...
Thomas Hepner
1

votes
0

answer
54

Views

large amount of data in Azure SQL > U-SQL

I am trying to find the best way to process in U-SQL a query that produces 450 millions rows. I use DataFactory V2 (pipeline) - USQL for transformation The problem : it takes an eternity to extract it into a csv file and to inject it back to the Azure SQL DB I wanted to know if it is possible to que...
EZL
1

votes
1

answer
246

Views

Modification and expiration time of a file stored in data lake store

I am using .NET API provided by Mircosoft to get the file info stored in data lake store in my code. These files are generated by usqljob job. when i am using the following statement: m_adlsFileSystemClient.FileSystem.GetFileStatus(m_adlsAccountName,fileName).FileStatus.ModificationTime then it giv...
Jai
1

votes
1

answer
255

Views

How to handle carriage return, linefeed within a quoted string

Multiple source systems I want to process using Azure Data Lake contain a carriage return, linefeed within a column. This causes Extract in ADLA to fail with the following error: E_RUNTIME_USER_EXTRACT_UNEXPECTED_ROW_DELIMITER Trying to find a working configuration to not be running into this issue...
RB84
1

votes
0

answer
59

Views

Why the position of ORDER BY clause is different between PERCENT_RANK and PERCENTILE_CONT

I noticed in all SQL-family languages (T-SQL, U-SQL, etc.), the ORDER_BY clause is required to be put inside the OVER clause for PERCENT_RANK, like: PERCENT_RANK( ) OVER ( [ partition_by_clause ] order_by_clause ) But for PERCENTILE_CONT and PERCENTILE_DISC, ORDER_BY clause needs to be put outsi...
Lifu Huang
1

votes
1

answer
171

Views

Nested JSON - Azure Data Lake - U-SQL Extraction to CSV

I've tried different methods to extract data from my JSON files and convert to CSV in U-SQL but they all seem to generate empty files or just output the header row. I was previously trying JSON tuples but since that generated empty files I am now trying to use the MultiLevelJsonExtractor. My JSON fi...
1

votes
2

answer
188

Views

msbuild using VSTS returns Error MSB4019

I'm trying to build CI/CD for Azure Data lake analytics - USQL code and when i build the code using Visual studio build option in VSTS getting the below error - Error MSB4019:The imported project 'C:\Users\buildguest\AppData\Roaming\Microsoft\DataLake\MsBuild\1.0\Usql.targets' was not found. My impo...
Arunachalam
1

votes
2

answer
252

Views

Azure Data lake analytics CI/CD

I'm trying to build CI/CD for Azure Data lake analytics - USQL code and when i build the code using Visual studio build option in VSTS getting the below error - Using the Private agent for taking the build - C:\Users\a.sivananthan\AppData\Roaming\Microsof...
Arunachalam
1

votes
1

answer
142

Views

USQL - Custom Outputter failed to find NewtonSoft

I have a USQL Job which reads json from Azure Blob and after some data manipulation writes a single line JSON file to ADLS. I have written a Custom Ouputter to write JSON File. This is how my CustomOutputter files look like: using Microsoft.Analytics.Interfaces; using Microsoft.Analytics.Types.Sql;...
Pratik
1

votes
0

answer
451

Views

Fetch Paging Data using Azure Data Factory

I created a Pipeline in Azure Data Factory V2. It will copy the data from Rest API and save this data in the form of JSON file in Azure Data Lake. Then I transform that JSON file using U-SQL and Copy that data into another folder in .csv format. My Pipeline. See the following Image of Pipeline. The...
Waqas Idrees
1

votes
1

answer
55

Views

exception without explicit reason when deploying U-SQL jobs to Azure by Python SDK

I am using python SDK to submit jobs to Azure using adlaJobClient , I have around 30 dynamic USQLs constructed using JINJA2, which I am populating in a list and then pushing them off to Azure using adlaJobClient one by one, The problem which I am facing is after a random number of successful deploym...
Abhinaba
1

votes
1

answer
80

Views

azure adl json files u-Sql error: 'Extract' on the user type 'Microsoft.Analytics.Samples.Formats.Json.MultiLevelJsonExtractor'

When I try to extract from json files I get the error: adl An unhandled exception from user code has been reported when invoking the method 'Extract' on the user type 'Microsoft.Analytics.Samples.Formats.Json.MultiLevelJsonExtractor' I have installed assembly but i'm always getting this error when I...
Fred
1

votes
1

answer
57

Views

Inserting JSON schema into U-SQL table

I want to insert JSON schema for my U-SQL table in DataLake Analysts tool. Here is my JSON schema DECLARE @json string= '{ 'definitions': {}, '$schema': 'http://json-schema.org/draft-06/schema#', '$id': 'http://getIQOS.com/IQOSAbandonedCartV1.json', 'title': 'CE:I:ORD:ABC', 'type': 'object', 'prope...
kiran kumar
1

votes
1

answer
187

Views

How to save byte[] as string in U-SQL

SQL script (Azure Data Lake Analytics) in which I extract too large text for string. So I use byte[]. But when I save results in CSV file, this text is BASE64 encoded. Is there a option to save it as simple string? (For saving I use Outputters.Csv() ). OR: Then I copy data (with Azure Data Factory)...
1

votes
0

answer
214

Views

Error Id: VertexFailedFast, Error Message: Vertex failed with a fail-fast error

when I run the Below U SQL I am getting 'Activity U-SQL1 failed: Error Id: VertexFailedFast, Error Message: Vertex failed with a fail-fast error.' The input schema has 7 columns. But still i am getting this error.I'm also skipping the first row because it contains headers. DECLARE @file_set_path st...
vignesh asokan
1

votes
1

answer
113

Views

USQL Unit testing with ADL tools for VS 2017 - Error after upgrading to 2.3.4000.x

One of the team member after upgrading the ADL tools for VS to version 2.3.4000.x, getting the below error.. Error : (-1,-1) 'E_CSC_SYSTEM_INTERNAL: Internal error! The ObjectManager found an invalid number of fixups. This usually indicates a problem in the Formatter.' Compile failed! Tried to d...
obulis
1

votes
1

answer
148

Views

Https not supported between Usql and Blob Storage, although it seems to be?

I'm finding this works: @searchlog = EXTRACT UserId int, Start DateTime, Region string, Query string, Duration int, Urls string, ClickedUrls string FROM @'wasb://[email protected]/SearchLog.tsv' USING Extractors.T...
Alex KeySmith
1

votes
0

answer
49

Views

How to use USQL database project that has credentials?

Since CREATE CREDENTIAL is deprecated, and the new project type outputs .usqldbpack to be run with PackageDeploymentTool.exe, how do we handle external SQL database sources and credentials? Database don't exists yet, so can't run. PowerShell to create credential on it. Previously I handled this by r...
SondreB
1

votes
0

answer
50

Views

Export Design to PDF feature from Visual Studio?

I wrote an U-SQL script in Visual Studio and it gives you the possibility to show a graph of it. The problem is that this graph is big... I would like to export it to PDF. Is that possible from within VS? Here is a screen grab of what the graph looks like:
john.dacost
1

votes
1

answer
38

Views

Unable to Extract simple Csv file using U-SQL

I have this csv file, Almost all the records are getting processed fine, however there are two cases in which i am experiencing an issue. Case 1: A record containing quotes within quotes: 'some data 'some data' some data' Case 2: A record containing comma within quotes: 'some data, some data some da...
user3261186
1

votes
1

answer
77

Views

U-SQL + Pandas Merge_asof

I am working with Azure Data Lake Analytics for the first time and I am unsure how to merge 2 datasets like I would with pandas in python. I am merging two datasets that have different timestamps but I need to line them up if they are within a specific timespan. This is straight forward in python. E...
David Baumgarten
1

votes
2

answer
104

Views

How can I pass a variable between 2 U-SQL scripts

I am trying to filter values in a table using values in another table. Since USQL doesn't allow to use another table with the WHERE IN statement, my thoughts were to use a usql function to create a list of values and then pass that along to my main script. Any ideas as to how I can pass the necessar...
Cristian Iosub
1

votes
2

answer
147

Views

Installing R-packages in Azure Data Lake Analytics

I have an issue with installing the below R-packages and reference them in an R-script I have encapsulated in a U-SQL-script. I succeeded in running a simple R-script in a U-SQL-job that required no special packages. Now I am trying to create an R-script that references dplyr, tdyr and reshape2. The...
JonJagd
1

votes
0

answer
59

Views

multiple file processing using ADF

I have created pipeline which does steps Copy files from azure blob storage and save in Azure data lake store then Then USql task pick that files and create summarize files in azure data lake store Next task pick data from that file and save in db I am passing 2 parameters windowStart and windowEnd...
BraveBoy
1

votes
0

answer
30

Views

Run R script in U-SQL

When I run a sample USQL using R extension , I get this error ****'message': 'Error Id: VertexFailedFast, Error Message: Vertex failed with a fail-fast error. '**** REFERENCE ASSEMBLY [ExtR]; //enable R extensions for the U-SQL Script //declare the R script as a string variable and pass it as a p...
Nima Zolghadr
1

votes
1

answer
32

Views

Unable to parse list of Json blocks in U-SQL

I have a file with list of json blocks and am stuck with processing/Reading them in U-Sql and writing to a text file. { 'id': '0001', 'type': 'donut', 'name': 'Cake', 'ppu': 0.55, 'batters': { 'batter': [ { 'id': '1001', 'type': 'Regular' }, { 'id': '1002', 'type': 'Chocolate' }, { 'id': '1003', 'ty...
Creator
1

votes
1

answer
98

Views

How read line separated json file from azure data lake and query using usql

I have ioT data in azure datalake structure as {date}/{month}/{day}/abbs. Json Each file has multiple records separated by new line .. How to read this data using usql and load into table and query. When I load it in usql table using ////.json will that load data into same table when new files add...
Amjath Khan
1

votes
1

answer
77

Views

Dynamic file name with custom outputter

I'm trying to process images (create thumbnail images) using u-sql with custom outputter and trying to output files with dynamic file name. My u-sql code look like this. REFERENCE ASSEMBLY [USQLAssemblies]; @image_out = SELECT USQLAssemblies.ImageOps.scaleImageTo(ImgData, 480, 480) AS thumbnail_imag...
Art
1

votes
0

answer
20

Views

Hint parallelization for U-SQL outputter

I have a custom u-sql outputter which has some reasonably heavy lifting to do. My understanding is that an outputter will naturally parallelize and create separate files, as stitching the files back together in Azure Data Lake Store is a quick operation. Running as either a custom outputter or proce...
Alex KeySmith
1

votes
1

answer
84

Views

Control of parallelization

I am running a custom processor on a rowset that does not seem to run in parallel. The underlying ~1GB text file is first read into a table that is partitioned via round robin. The 'Extract' runs on 200 vertices but then (under 'Aggregate' node) the processing [that does various complex computations...
chi
1

votes
1

answer
258

Views

Can't access cutom file in U-SQL code-behind using custom assemblies?

I am registering and using custom assemblies in U-SQL that access a file to fetch data from. The data file is uploaded as an 'Addition File' when registering the assembly with it's dependencies (I am using VS 2015). However, the job fails with a System.IO.FileNotFoundException, with the custom assem...
Tayyab Anwar
1

votes
1

answer
108

Views

How to pass count to U-SQL Applier?

I want to pass data count to custom applier but I am not sure how to pass it. Here is my sample code where I am calculating count in @count and passing it to CsvApplier constructor but it is not working. Is there any way to achieve this in U-SQL? Note that it is not working so I am looking DECLARE @...
Jamil
1

votes
1

answer
310

Views

Querying very large xml files

I have a merged very large xml file on scale of GB's. I am using following code with xpath queries to read and process data. IColumn column = output.Schema.FirstOrDefault(col => col.Type != typeof(string)); if (column != null) { throw new ArgumentException(string.Format('Column '{0}' must be of type...
1

votes
1

answer
135

Views

Creating Spark job in Data Lake instead of a U-SQL job

Is it possible to create Spark job in Data Lake instead of a U-SQL job ?
Midhun Murali
1

votes
2

answer
431

Views

Can we add range of partitions to a table in ADL from dynamic data

Is it possible to add partitions dynamically instead of fixed to specific static data. For example, if we need to create partitions for all dates from different CSV records.
Ahsan Abbas
1

votes
1

answer
440

Views

What is the purpose of a U-SQL Reducer?

I haven't been able to find any documentation or samples for the use of Reducers in U-SQL. How is a Reducer different from an Applier, because from the function signatures, they both receive one row at a time. My use case is in the following question: Azure Data Lake Analytics: Combine overlapping t...
Tayyab Anwar
1

votes
1

answer
683

Views

U-SQL Tables vs SQL Data Warehouse

So here's where I'm at. I'm storing huge amounts of data in Data Lake Store. But when I want to make a report (it can be a month's worth), I want to schematize it into a table to refer to over and over again when querying upon it. Should I just use the built in database feature that Data Lake Analy...
AyeMarciMar
1

votes
1

answer
156

Views

Runtime Error for U-SQL Scripts when Repeatedly Executing in Local Environment

We have a simple U-SQL Migration Script that: Selects data from a staging table in our ADL database Truncates the staging table Inserts contents to a persisted table in ADL When we run this script after running our loading script for our staging table, the script runs successfully and the data is in...
X. Ou
1

votes
1

answer
95

Views

How to iterate through the result of a SELECT query to find a row pattern using U-SQL

I have the result of a SELECT query in a variable, and now I want to iterate through the query result row by row to do some processing, like finding a particular pattern. For example, the pattern could be the following: a, b, c, d, e b, c, d, e, f c, d, e, f, g And the result of the SELECT query in...
Alvin Chin

View additional questions