Stack Overflow
Stack Overflow

Questions tagged [vsql]

vsql is a Vertica front-end client utility for Unix-based systems that provides meta-commands and various shell-like features that facilitate writing scripts and automating a variety of tasks related to SQL.

vsql can be installed on Unix-based systems such as Solaris, HP-UX, AIX and Mac OSX. It is typically installed as part of the HP Vertica server rpm (Red Hat Package Manager).

See using vsql for more information.

54 questions
6
votes
2 answers

Return Top N rows for each group (Vertica/vsql)

Familiar question, but with Vertica. I'd like to return the top 5 geo_country rows based on sum(imps) for each tag_id. This is the query I started: SELECT tag_id, geo_country, SUM(imps) AS imps, RANK() OVER (PARTITION BY tag_id…
ChrisArmstrong
  • 2,491
  • 8
  • 37
  • 60
5
votes
3 answers

How do I display the query time when a query completes in Vertica?

When using vsql, I would like to see how long a query took to run once it completes. For example when i run: select count(distinct key) from schema.table; I would like to see an output like: 5678 (1 row) total query time: 55 seconds. If this is not…
Ilya Kaplun
  • 51
  • 1
  • 5
5
votes
1 answer

Display vertica records vertically in vsql

I am look for a command in vsql which is equivalent of \g in mysql console, because I need display the result set vertically, looking like this:
Jade Tang
  • 321
  • 4
  • 23
5
votes
2 answers

SQL Runnnig the same Query multiple times

I have a query that gives me the COUNT between two dates, start_date & end_date and is grouped by various columns. Is there any way I can get the COUNT for each day ? As in say start_date is date1 and end_date is date5, so I need to run the query…
Neha
  • 233
  • 1
  • 3
  • 11
4
votes
1 answer

How to escape quotes in strings in vertica (vsql)?

So I need to insert some values into a vertica database (via vsql), which may contain quotes and all sorts of special characters. But vertica does not seem to understand character escaping. For example: rpt=> select "asdasda\"asdasdad" from…
Ivan P
  • 1,920
  • 2
  • 15
  • 19
3
votes
1 answer

Vertica include column names in output, but not row count footer

I am running some Vertica SQL queries from the command line and saving them to CSVs. I want to keep column names, so I do not use the -t option. The problem is that I now am also left with the footer which gives the row count (e.g. (20 rows). I…
lhay86
  • 696
  • 2
  • 5
  • 18
3
votes
2 answers

How to exclude the total row information from VSQL output

I am using VSQL to extract data from a table in CSV format using this command: vsql -h [host_address] -d [db_name] -u [user_name] -w [password] -A -F , -t -f script_to_extract_data.sql -o output.csv However, it outputs column headers, data rows…
user1330974
  • 2,500
  • 5
  • 32
  • 60
3
votes
4 answers

vsql error handling (No such file or directory)

I'm using shell scripting to upload a query's results to my database. In the script, first I save my query's results into a csv file and then upload the file into another database. After each step, I send a notification email to the user. The…
saghar
  • 67
  • 1
  • 3
  • 10
2
votes
0 answers

Vertica COPY rejects all data when source is gzip

I'm trying to upload a file to vertica using the vsql CLI. My command looks like this (it's all one line, but I'm breaking it up with backslashes here for ease of reading): vsql "-c set timezone to 'UTC'; \ copy etl.test_data1…
quaintm
  • 677
  • 1
  • 7
  • 18
2
votes
1 answer

Record Separation in VSQL Export; CR LF

I'm exporting using vsql and I've included an explicit ' \pset recordsep '\n' ' in the header. I'd expect the resulting text file to have just 'LF' at the end of each record, but the records end in 'CRLF' instead, despite the lack of '\r' in the…
TroutFishing
  • 43
  • 4
1
vote
1 answer

Unable to use TransformFunction in vertica_sdk

What i am trying to do is execute vertica's string tokenizer example which is written in python. Here is the link to the said…
Rajdeep Sengupta
  • 45
  • 7
1
vote
1 answer

Is there any "Row Processed Count" variable for the last executed query in Vertica database, either in system variables or system tables?

I want to capture total row processed count of the last ran query in Vertica VSQL shell. I know this can be done programmatically, but that is not the question here. For the answer, any system table pointers will also help, or Vertica maintained…
minatverma
  • 1,090
  • 13
  • 24
1
vote
0 answers

VSql client hangs after restore

After restoring a database from a backup using vbr utility, when I start the database, queries fail to execute. The vertica log shows the query but it is not executed. Moreover, I cannot exit vsql or cancel the command and the cluster no longer…
Bhavi Daftery Jagwani
  • 105
  • 6
1
vote
1 answer

Variable in sql file with single quote

SELECT SWAP_PARTITIONS_BETWEEN_TABLES (':SCHEMA_NAME.:TABLE_NAME',:PARTITION_KEY,:PARTITION_KEY, ':SCHEMA_NAME.:TABLE_NAME'); This is a vertica query in sql file :SCHEMA_NAME and :TABLE_NAME in sql file is not getting replaced by the argument…
Swathi8038
  • 96
  • 2
  • 11
1
vote
2 answers

SQL (Vertica) - Calculate number of users who returned to the app at least x days in the past 7 days

Suppose I have my table like: uid day_used_app --- ------------- 1 2012-04-28 1 2012-04-29 1 2012-04-30 2 2012-04-29 2 2012-04-30 2 2012-05-01 2 2012-05-21 2 2012-05-22 …
boldbrandywine
  • 322
  • 1
  • 14
1
2 3 4