kandi background
Explore Kits

usql | Universal commandline interface for SQL databases | SQL Database library

 by   xo Go Version: v0.9.5 License: MIT

 by   xo Go Version: v0.9.5 License: MIT

Download this library from

kandi X-RAY | usql Summary

usql is a Go library typically used in Database, SQL Database, PostgresSQL, Oracle applications. usql has no bugs, it has no vulnerabilities, it has a Permissive License and it has medium support. You can download it from GitHub.
Installing | Building | Using | Database Support | Features and Compatibility | Releases | Contributing.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • usql has a medium active ecosystem.
  • It has 6967 star(s) with 262 fork(s). There are 118 watchers for this library.
  • There were 3 major release(s) in the last 12 months.
  • There are 58 open issues and 183 have been closed. On average issues are closed in 44 days. There are 1 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of usql is v0.9.5
usql Support
Best in #SQL Database
Average in #SQL Database
usql Support
Best in #SQL Database
Average in #SQL Database

quality kandi Quality

  • usql has 0 bugs and 0 code smells.
usql Quality
Best in #SQL Database
Average in #SQL Database
usql Quality
Best in #SQL Database
Average in #SQL Database

securitySecurity

  • usql has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
  • usql code analysis shows 0 unresolved vulnerabilities.
  • There are 0 security hotspots that need review.
usql Security
Best in #SQL Database
Average in #SQL Database
usql Security
Best in #SQL Database
Average in #SQL Database

license License

  • usql is licensed under the MIT License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
usql License
Best in #SQL Database
Average in #SQL Database
usql License
Best in #SQL Database
Average in #SQL Database

buildReuse

  • usql releases are available to install and integrate.
  • Installation instructions are not available. Examples and code snippets are available.
  • It has 15401 lines of code, 512 functions and 124 files.
  • It has high code complexity. Code complexity directly impacts maintainability of the code.
usql Reuse
Best in #SQL Database
Average in #SQL Database
usql Reuse
Best in #SQL Database
Average in #SQL Database
Top functions reviewed by kandi - BETA

Coming Soon for all Libraries!

Currently covering the most popular Java, JavaScript and Python libraries. See a SAMPLE HERE.
kandi's functional review helps you automatically verify the functionalities of the libraries and avoid rework.

usql Key Features

source connection string

destination connection string

source query

destination table name, optionally with columns

Installing via Release

copy iconCopydownload iconDownload
$ usql
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicuuc.68.dylib
  Referenced from: /Users/user/.local/bin/usql
  Reason: image not found
Abort trap: 6

Installing via Homebrew (macOS and Linux)

copy iconCopydownload iconDownload
# install usql with "most" drivers
$ brew install xo/xo/usql

Installing via Scoop (Windows)

copy iconCopydownload iconDownload
# install scoop if not already installed
iex (new-object net.webclient).downloadstring('https://get.scoop.sh')

scoop install usql

Installing via Go

copy iconCopydownload iconDownload
# install usql with basic database support
# includes PostgreSQL, Oracle Database, MySQL, MS SQL, and SQLite3 drivers
$ GO111MODULE=on go get github.com/xo/usql

Building

copy iconCopydownload iconDownload
# install all drivers
$ GO111MODULE=on go get -tags all github.com/xo/usql

# install with most drivers
$ GO111MODULE=on go get -tags most github.com/xo/usql

# install with base drivers and additional support for Oracle Database and ODBC
$ GO111MODULE=on go get -tags 'godror odbc' github.com/xo/usql

Database Support

copy iconCopydownload iconDownload
$ cd $GOPATH/src/github.com/xo/usql
$ export GO111MODULE=on
# build excluding the base drivers, and including cassandra and moderncsqlite
$ go build -tags 'no_postgres no_oracle no_sqlserver no_sqlite3 cassandra moderncsqlite'
# show built driver support
$ ./usql -c '\drivers'
Available Drivers:
  cql [ca, scy, scylla, datastax, cassandra]
  memsql (mysql) [me]
  moderncsqlite [mq, sq, file, sqlite, sqlite3, modernsqlite]
  mysql [my, maria, aurora, mariadb, percona]
  tidb (mysql) [ti]
  vitess (mysql) [vt]

Using

copy iconCopydownload iconDownload
# connect to a postgres database
$ usql postgres://booktest@localhost/booktest

# connect to an oracle database
$ usql oracle://user:pass@host/oracle.sid

# connect to a postgres database and run the commands contained in script.sql
$ usql pg://localhost/ -f script.sql

Connecting to Databases

copy iconCopydownload iconDownload
   driver+transport://user:pass@host/dbname?opt1=a&opt2=b
   driver:/path/to/file
   /path/to/file

Connection Examples

copy iconCopydownload iconDownload
# connect to a postgres database
$ usql pg://user:pass@host/dbname
$ usql pgsql://user:pass@host/dbname
$ usql postgres://user:pass@host:port/dbname
$ usql pg://
$ usql /var/run/postgresql
$ usql pg://user:pass@host/dbname?sslmode=disable # Connect without SSL

# connect to a mysql database
$ usql my://user:pass@host/dbname
$ usql mysql://user:pass@host:port/dbname
$ usql my://
$ usql /var/run/mysqld/mysqld.sock

# connect to a sqlserver database
$ usql sqlserver://user:pass@host/instancename/dbname
$ usql ms://user:pass@host/dbname
$ usql ms://user:pass@host/instancename/dbname
$ usql mssql://user:pass@host:port/dbname
$ usql ms://

# connect to a sqlserver database using Windows domain authentication
$ runas /user:ACME\wiley /netonly "usql mssql://host/dbname/"

# connect to a oracle database
$ usql or://user:pass@host/sid
$ usql oracle://user:pass@host:port/sid
$ usql or://

# connect to a cassandra database
$ usql ca://user:pass@host/keyspace
$ usql cassandra://host/keyspace
$ usql cql://host/
$ usql ca://

# connect to a sqlite database that exists on disk
$ usql dbname.sqlite3

# NOTE: when connecting to a SQLite database, if the "<driver>://" or
# "<driver>:" scheme/alias is omitted, the file must already exist on disk.
#
# if the file does not yet exist, the URL must incorporate file:, sq:, sqlite3:,
# or any other recognized sqlite3 driver alias to force usql to create a new,
# empty database at the specified path:
$ usql sq://path/to/dbname.sqlite3
$ usql sqlite3://path/to/dbname.sqlite3
$ usql file:/path/to/dbname.sqlite3

# connect to a adodb ole resource (windows only)
$ usql adodb://Microsoft.Jet.OLEDB.4.0/myfile.mdb
$ usql "adodb://Microsoft.ACE.OLEDB.12.0/?Extended+Properties=\"Text;HDR=NO;FMT=Delimited\""

# connect with ODBC driver (requires building with odbc tag)
$ cat /etc/odbcinst.ini
[DB2]
Description=DB2 driver
Driver=/opt/db2/clidriver/lib/libdb2.so
FileUsage = 1
DontDLClose = 1

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
# connect to db2, postgres databases using ODBC
$ usql odbc+DB2://user:pass@localhost/dbname
$ usql odbc+PostgreSQL+ANSI://user:pass@localhost/dbname?TraceFile=/path/to/trace.log

Executing Queries and Commands

copy iconCopydownload iconDownload
$ usql sqlite://example.sqlite3
Connected with driver sqlite3 (SQLite3 3.17.0)
Type "help" for help.

sq:example.sqlite3=> create table test (test_id int, name string);
CREATE TABLE
sq:example.sqlite3=> insert into test (test_id, name) values (1, 'hello');
INSERT 1
sq:example.sqlite3=> select * from test;
  test_id | name
+---------+-------+
        1 | hello
(1 rows)

sq:example.sqlite3=> select * from test
sq:example.sqlite3-> \p
select * from test
sq:example.sqlite3-> \g
  test_id | name
+---------+-------+
        1 | hello
(1 rows)

sq:example.sqlite3=> \c postgres://booktest@localhost
error: pq: 28P01: password authentication failed for user "booktest"
Enter password:
Connected with driver postgres (PostgreSQL 9.6.6)
pg:booktest@localhost=> select * from authors;
  author_id |      name
+-----------+----------------+
          1 | Unknown Master
          2 | blah
          3 | aoeu
(3 rows)

pg:booktest@localhost=>

Backslash Commands

copy iconCopydownload iconDownload
$ usql
Type "help" for help.

(not connected)=> \?
General
  \q                                   quit usql
  \copyright                           show usql usage and distribution terms
  \drivers                             display information about available database drivers

Query Execute
  \g [(OPTIONS)] [FILE] or ;           execute query (and send results to file or |pipe)
  \crosstabview [(OPTIONS)] [COLUMNS]  execute query and display results in crosstab
  \G [(OPTIONS)] [FILE]                as \g, but forces vertical output mode
  \gexec                               execute query and execute each value of the result
  \gset [PREFIX]                       execute query and store results in usql variables
  \gx [(OPTIONS)] [FILE]               as \g, but forces expanded output mode
  \watch [(OPTIONS)] [DURATION]        execute query every specified interval

Query Buffer
  \e [FILE] [LINE]                     edit the query buffer (or file) with external editor
  \p                                   show the contents of the query buffer
  \raw                                 show the raw (non-interpolated) contents of the query buffer
  \r                                   reset (clear) the query buffer
  \w FILE                              write query buffer to file

Help
  \? [commands]                        show help on backslash commands
  \? options                           show help on usql command-line options
  \? variables                         show help on special variables

Input/Output
  \echo [-n] [STRING]                  write string to standard output (-n for no newline)
  \qecho [-n] [STRING]                 write string to \o output stream (-n for no newline)
  \warn [-n] [STRING]                  write string to standard error (-n for no newline)
  \o [FILE]                            send all query results to file or |pipe
  \i FILE                              execute commands from file
  \ir FILE                             as \i, but relative to location of current script

Informational
  \d[S+] [NAME]                        list tables, views, and sequences or describe table, view, sequence, or index
  \da[S+] [PATTERN]                    list aggregates
  \df[S+] [PATTERN]                    list functions
  \di[S+] [PATTERN]                    list indexes
  \dm[S+] [PATTERN]                    list materialized views
  \dn[S+] [PATTERN]                    list schemas
  \ds[S+] [PATTERN]                    list sequences
  \dt[S+] [PATTERN]                    list tables
  \dv[S+] [PATTERN]                    list views
  \l[+]                                list databases

Formatting
  \pset [NAME [VALUE]]                 set table output option
  \a                                   toggle between unaligned and aligned output mode
  \C [STRING]                          set table title, or unset if none
  \f [STRING]                          show or set field separator for unaligned query output
  \H                                   toggle HTML output mode
  \T [STRING]                          set HTML <table> tag attributes, or unset if none
  \t [on|off]                          show only rows
  \x [on|off|auto]                     toggle expanded output

Transaction
  \begin                               begin a transaction
  \commit                              commit current transaction
  \rollback                            rollback (abort) current transaction

Connection
  \c URL                               connect to database with url
  \c DRIVER PARAMS...                  connect to database with SQL driver and parameters
  \Z                                   close database connection
  \password [USERNAME]                 change the password for a user
  \conninfo                            display information about the current database connection

Operating System
  \cd [DIR]                            change the current working directory
  \setenv NAME [VALUE]                 set or unset environment variable
  \! [COMMAND]                         execute command in shell or start interactive shell
  \timing [on|off]                     toggle timing of commands

Variables
  \prompt [-TYPE] <VAR> [PROMPT]       prompt user to set variable
  \set [NAME [VALUE]]                  set internal variable, or list all if no parameters
  \unset NAME                          unset (delete) internal variable

Features and Compatibility

copy iconCopydownload iconDownload
$ usql
(not connected)=> \set
(not connected)=> \set FOO bar
(not connected)=> \set
FOO = 'bar'
(not connected)=> \unset FOO
(not connected)=> \set
(not connected)=>

Azure DataLake Query Expression | Error Processing Query

copy iconCopydownload iconDownload
Fetch :=  
       'FETCH' ['FIRST' | 'NEXT']  integer_or_long_literal ['ROW' | 'ROWS']  
       ['ONLY'].
   
query_expression = 'SELECT * from DataLakeStorage ORDER BY mail OFFSET 5 FETCH NEXT 5 ROWS ONLY;
-----------------------
Fetch :=  
       'FETCH' ['FIRST' | 'NEXT']  integer_or_long_literal ['ROW' | 'ROWS']  
       ['ONLY'].
   
query_expression = 'SELECT * from DataLakeStorage ORDER BY mail OFFSET 5 FETCH NEXT 5 ROWS ONLY;

U-SQL random sample of rows

copy iconCopydownload iconDownload
@outsearchlog =
    SELECT *          
    FROM @searchlog
    SAMPLE UNIFORM (0.1);
SELECT *
FROM yourTable
TABLESAMPLE( 10 PERCENT );
-----------------------
@outsearchlog =
    SELECT *          
    FROM @searchlog
    SAMPLE UNIFORM (0.1);
SELECT *
FROM yourTable
TABLESAMPLE( 10 PERCENT );

Database locked in vb.net when trying to update data in vb.net

copy iconCopydownload iconDownload
Private ConStr As String = "Your connection string"

Public Function updateguest(FirstName As String, ID As Integer) As Integer
    Dim Result As Integer
    Dim usql As String = "UPDATE Customers SET fname = @fname WHERE CustomerID = @ID;"
    Using con As New SQLiteConnection(ConStr),
            cmd As New SQLiteCommand(usql, con)
        cmd.Parameters.Add("@fname", DbType.String).Value = FirstName
        cmd.Parameters.Add("@ID", DbType.Int32).Value = ID
        con.Open()
        Result = cmd.ExecuteNonQuery
    End Using
    Return Result
End Function

Private Sub IbtnUpdate_Click(sender As Object, e As EventArgs) Handles ibtnUpdate.Click
    Try
        Dim Result = updateguest(txtFName.Text, CInt(txtSearchID.Text))
        If Result > 0 Then
            MsgBox("New RECORD HAS BEEN UPDATED!")
        Else
            MsgBox("NO RECORD HAS BEEN UPDATDD!")
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

How to find unit test dll in Azure Devops when copying test artifact to run on multiple agents (YAML)

copy iconCopydownload iconDownload
- task: DownloadBuildArtifacts@0

  displayName: 'Download Build Artifacts'

  inputs:

    artifactName: drop

    downloadPath: '$(System.DefaultWorkingDirectory)'
  - task: VSTest@2
    inputs:
      platform: '$(buildPlatform)'
      configuration: '$(buildConfiguration)'
      searchFolder: '$(System.ArtifactsDirectory)'
      runOnlyImpactedTests: true
      runInParallel: true
-----------------------
- task: DownloadBuildArtifacts@0

  displayName: 'Download Build Artifacts'

  inputs:

    artifactName: drop

    downloadPath: '$(System.DefaultWorkingDirectory)'
  - task: VSTest@2
    inputs:
      platform: '$(buildPlatform)'
      configuration: '$(buildConfiguration)'
      searchFolder: '$(System.ArtifactsDirectory)'
      runOnlyImpactedTests: true
      runInParallel: true

Join files from different pathes in USQL

copy iconCopydownload iconDownload
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

DECLARE @input string = "/Data/2018/{*}/2/mydata.json";

USING Microsoft.Analytics.Samples.Formats.Json;

@a  =
   EXTRACT EventCategory string
          , EventAction string
          , EventLabel string
    FROM  @input
    USING new JsonExtractor()

 OUTPUT @a
   TO "/Output/mydata.Csv"
  USING Outputters.Csv(outputHeader:true);  
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

USING Microsoft.Analytics.Samples.Formats.Json;

@a  =
   EXTRACT EventCategory string
          , EventAction string
          , EventLabel string
          , date DateTime
    FROM  "/Data/{date:yyyy}/{date:MM}/{date:dd}/mydata.json"
    USING new JsonExtractor()

 OUTPUT @a
   TO "/Output/mydata.Csv"
  USING Outputters.Csv(outputHeader:true);  
-----------------------
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

DECLARE @input string = "/Data/2018/{*}/2/mydata.json";

USING Microsoft.Analytics.Samples.Formats.Json;

@a  =
   EXTRACT EventCategory string
          , EventAction string
          , EventLabel string
    FROM  @input
    USING new JsonExtractor()

 OUTPUT @a
   TO "/Output/mydata.Csv"
  USING Outputters.Csv(outputHeader:true);  
REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

USING Microsoft.Analytics.Samples.Formats.Json;

@a  =
   EXTRACT EventCategory string
          , EventAction string
          , EventLabel string
          , date DateTime
    FROM  "/Data/{date:yyyy}/{date:MM}/{date:dd}/mydata.json"
    USING new JsonExtractor()

 OUTPUT @a
   TO "/Output/mydata.Csv"
  USING Outputters.Csv(outputHeader:true);  

Community Discussions

Trending Discussions on usql
  • Azure DataLake Query Expression | Error Processing Query
  • Access VBA Select SubForm Records Update with Input Tag
  • U-SQL random sample of rows
  • Database locked in vb.net when trying to update data in vb.net
  • How to find unit test dll in Azure Devops when copying test artifact to run on multiple agents (YAML)
  • Join files from different pathes in USQL
Trending Discussions on usql

QUESTION

Azure DataLake Query Expression | Error Processing Query

Asked 2021-Jun-21 at 15:58

I got some data in blob storage under data lake in CSV, data format is

mail,usertype,license

Data is huge, so I'm implementing pagination in Django to render it, I don't want my compute unit to fetch all half a mil rows every time which makes the website slow. To improve pagination. Right now when I am querying data based on rows with limit then I'm getting an error

Syntax error!
RequestId:59f9d9e5-601e-0015-4f0f-64fd1d000000
Time:2021-06-18T06:56:56.9344470Z
ErrorCode:ErrorProcessingQueryExpression
Error:None

Here's my query to pick data of the 5th to 10th row for a test.

query_expression = "SELECT * from DataLakeStorage limit 5,10"

Updating the question,

I updated the query but in Azure Data Lake but getting errors, seems Datalake is using uSQL

filesystem_client = service_client.get_file_system_client(
            filesystem_name)
        file_client = service_client.get_file_client(
            filesystem_name, "csvfile.csv")
        query_expression = 'SELECT * from DataLakeStorage ORDER BY mail LIMIT 5, 10'
        # reader = file_client.query_file(query_expression)
        input_format = DelimitedTextDialect(
            delimiter=",",
            lineterminator="\n",
            escapechar="",
            has_header=True,
        )
        output_format = DelimitedJsonDialect(delimiter="\n")
        reader = file_client.query_file(
            query_expression,
            file_format=input_format,
            # output_format=output_format,
        )
        # [END query]
        content = reader.readall()
        content = content.decode('utf-8')

and here's the error which i am getting

syntax error!
RequestId:6d868cb6-701e-008f-10af-6663c4000000
Time:2021-06-21T15:10:12.6586583Z
ErrorCode:ErrorProcessingQueryExpression
Error:None

ANSWER

Answered 2021-Jun-21 at 15:58

Similar to Other SQL dialects such as TOP() or a LIMIT clause, U-SQL currently offers the FETCH clause.

The OFFSET/FETCH clause is the ANSI SQL-conformant way to specify getting the first number of rows. If the OFFSET x ROWS clause is not specified, it defaults to OFFSET 0 ROWS. If the FETCH clause is not specified, then all the rows starting after the skipped rows are being returned.

Syntax:

Fetch :=  
       'FETCH' ['FIRST' | 'NEXT']  integer_or_long_literal ['ROW' | 'ROWS']  
       ['ONLY'].
   

In your case:

query_expression = 'SELECT * from DataLakeStorage ORDER BY mail OFFSET 5 FETCH NEXT 5 ROWS ONLY;
  • FETCH command is to sequentially select the mentioned number of rows.
  • NEXT is for the cursor to understand that it has to fetch the next number of rows.
  • ONLY is for the cursor to understand that it has to close the FETCH operation.

Note:

  • OFFSET Can be used without FETCH but FETCH Can be used without OFFSET.
  • The OFFSET and FETCH clauses allow specifying integer or long literal values. The value for the FETCH clause has to be in the range [1, 10000], otherwise an error is raised.

Refer: ORDER BY and OFFSET/FETCH Clause (U-SQL)

Source https://stackoverflow.com/questions/68030729

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install usql

You can download it from GitHub.

Support

usql works with all Go standard library compatible SQL drivers supported by github.com/xo/dburl.

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Share this Page

share link
Reuse Pre-built Kits with usql
Consider Popular SQL Database Libraries
Try Top Libraries by xo
Compare SQL Database Libraries with Highest Support
Compare SQL Database Libraries with Highest Quality
Compare SQL Database Libraries with Highest Security
Compare SQL Database Libraries with Permissive License
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.