Hlavní navigace

Zpracování tabulek uložených v CSV i TSV pomocí SQL příkazů nástrojem „q“

13. 10. 2020
Doba čtení: 41 minut

Sdílet

 Autor: John De Lancie
V dnešní části poněkud nepravidelně vycházejícího seriálu o užitečných utilitách pro příkazovou řádku si popíšeme nástroj nazvaný „q“. Ten slouží pro spouštění SQL dotazů nikoli nad databází, ale přímo nad soubory CSV a TSV.

Obsah

1. Zpracování tabulek uložených v CSV i TSV pomocí SQL příkazů nástrojem „q“

2. Formáty CSV a TSV i jejich derivace

3. Instalace nástroje q

4. Lokální instalace, použití pip3

5. Základní kontrola instalace nástroje q

6. Tabulky ve formátu TSV použité v příkladech

7. Jednoduché dotazy nad tabulkou uloženou ve formátu TSV

8. Použití klauzule order by

9. Klauzule order by a sloupce s numerickými hodnotami

10. Omezení počtu řádků ve výsledku dotazu klauzulí limit

11. Podmínka specifikovaná klauzulí where

12. Agregační funkce

13. Tisk výsledků ve formě dále zpracovatelné tabulky

14. Naformátování výstupu pro zlepšení čtení výsledné tabulky

15. Práce se vstupními soubory CSV

16. Příklad dotazů nad CSV soubory

17. Klauzule distinct a group by

18. Spojení tabulek v dotazu klauzulí join

19. Použití klauzule join

20. Odkazy na Internetu

1. Zpracování tabulek uložených v CSV i TSV pomocí SQL příkazů nástrojem „q“

V seriálu o nástrojích určených pro vylepšení zpracování dat v příkazové řádce popř. ze shell skriptů jsme se již seznámili s nástrojem nazvaným jq. Tento nástroj umožňuje zpracovávat soubory ve formátu JSON, získávat z nich data, provádět transformace dat atd. Jedná se skutečně o užitečný nástroj, který do jisté míry sekunduje podobně koncipovaným nástrojům určeným pro zpracování XML.

Mnohdy (a čím dál tím častěji) se ovšem setkáme s nutností nějakým způsobem získat informace ze souborů typu CSV popř. TSV (viz další kapitolu), které obsahují data reprezentovaná tabulkami. Pro samotné prohlížení lze použít například VisiData, ovšem u rozsáhlejších tabulek by bylo vhodné využít nějaký vhodný dotazovací jazyk. Samozřejmě by bylo možné vytvořit nástroj s vlastním dotazovacím jazykem (což je nápad, který má – bohužel – mnoho firem a vývojářů; výsledky bývají tristní), ale výhodnější bude použít lety ověřený a známý SQL. A právě pro dotazy nad textovými soubory s daty s využitím SQL je určen nástroj nazvaný q, jímž se budeme zabývat dnes.

Poznámka: díky q lze na tabulky ukládané do CSV/TSV pohlížet jako na relační databázi, ovšem je nutné poznamenat, že se spíše jedná o ad-hoc řešení určené pro dotazy nad daty a nikoli o skutečnou databázi se schématem, indexy, ACL, podporou ACIDu atd. Hranice mezi tím, zda dotazy spouštět přímo nad CSV/TSV či zda provést import dat do skutečné relační databáze, je ovšem pro každého jiná.

2. Formáty CSV a TSV i jejich derivace

Nástroj q dokáže zpracovávat data uložená v textových souborech, v nichž jsou jednotlivé položky záznamů oddělené nějakým známým znakem. Může se jednat o soubory CSV (s určitými omezeními, o kterých si řekneme v následujícím textu), soubory TSV, popř. o soubory, v nichž jsou použity odlišné znaky ve formě oddělovačů. Uveďme si nyní typické příklady:

CSV neboli Comma-Separated Values [1] je jedním z nejčastěji používaných souborových formátů v této oblasti, a to přesto, že je export a import CSV v některých případech problematický (například některé české mutace Excelu namísto čárek používají středníky, problémy nastávají s buňkami obsahujícími znaky pro konec řádku atd.). Tyto soubory jsou mnohdy obrovské, ovšem s jejich zpracováním v nástroji q nebývají ani v tomto případě problémy. Pokud máte při importu či exportu potíže se zpracováním CSV, můžete použít několik přepínačů příkazového řádku s přesnou specifikací, jak se má konverze provést (viz další text). Zpracovávat lze i CSV soubory obsahující buňky obsahující konce řádků.

Příklad souboru CSV, konkrétně tabulky i s řádkem obsahujícím hlavičky (ty ovšem někdy nemusí být přítomny):

fips,name,total_population,margin_of_error
31001,"Adams County, NE",31299,0
31003,"Antelope County, NE",6655,0
31005,"Arthur County, NE",490,64
31007,"Banner County, NE",778,73
31009,"Blaine County, NE",584,76
31011,"Boone County, NE",5473,0
31013,"Box Butte County, NE",11345,0
31015,"Boyd County, NE",2025,114
31017,"Brown County, NE",3183,97
31019,"Buffalo County, NE",46330,0
31021,"Burt County, NE",6829,0
31023,"Butler County, NE",8345,0
31025,"Cass County, NE",25214,0
31027,"Cedar County, NE",8807,0
31029,"Chase County, NE",3964,0
31031,"Cherry County, NE",5722,0
31033,"Cheyenne County, NE",10038,0
31035,"Clay County, NE",6497,0

TSV neboli Tab-Separated Values [2] [3] je velmi podobným formátem, ovšem s tím rozdílem, že oddělovačem jednotlivých buněk je znak tabulátoru. Podobně jako v případě CSV i zde existuje několik voleb zadávaných na příkazovém řádku, které ovlivňují způsob importu (zda tabulka obsahuje hlavičky sloupců atd.). Navíc mohou být v záznamech obsaženy i formátovací znaky, před které se zapisuje zpětné lomítko.

Příklad souboru TSV (zobrazení pro situaci, kdy jsou tabelační zarážky umístěné na každý osmý sloupec):

Sepal length    Sepal width     Petal length    Petal width     Species
5.1     3.5     1.4     0.2     I. setosa
4.9     3.0     1.4     0.2     I. setosa
4.7     3.2     1.3     0.2     I. setosa
4.6     3.1     1.5     0.2     I. setosa
5.0     3.6     1.4     0.2     I. setosa

Ve skutečnosti mohou být znaky TAB zobrazeny různě. Pokud použijeme tabelační zarážky umisťované na každém šestnáctém sloupci, bude ta stejná zdrojová tabulka zobrazena takto:

Sepal length    Sepal width     Petal length    Petal width     Species
5.1             3.5             1.4             0.2             I. setosa
4.9             3.0             1.4             0.2             I. setosa
4.7             3.2             1.3             0.2             I. setosa
4.6             3.1             1.5             0.2             I. setosa
5.0             3.6             1.4             0.2             I. setosa

Existuje i mnoho aplikací, v nichž jsou tabulková data uložena ve formě běžných textových souborů s nějakými oddělovači odlišnými od výše zmíněného tabulátoru (relativně často se jedná o středníky, dvojtečky nebo o znak |). Buď se jedná o zobecnění formátů CSV a TSV [4], nebo může mít textový soubor podobu naformátovaných sloupců s pevnou délkou (a tedy bez problémů čitelných uživatelem).

Příkladem takového souboru je například https://www.cnb.cz/cs/finan­cni_trhy/devizovy_trh/kur­zy_devizoveho_trhu/denni_kur­z.txt.

09.10.2020 #196
země|měna|množství|kód|kurz
Austrálie|dolar|1|AUD|16,526
Brazílie|real|1|BRL|4,120
Bulharsko|lev|1|BGN|13,862
Čína|žen-min-pi|1|CNY|3,430
Dánsko|koruna|1|DKK|3,643
EMU|euro|1|EUR|27,110
Filipíny|peso|100|PHP|47,490
Hongkong|dolar|1|HKD|2,966
Chorvatsko|kuna|1|HRK|3,579
Indie|rupie|100|INR|31,449
Indonesie|rupie|1000|IDR|1,563
Island|koruna|100|ISK|16,652
Izrael|nový šekel|1|ILS|6,802
Japonsko|jen|100|JPY|21,694
Jižní Afrika|rand|1|ZAR|1,395
Kanada|dolar|1|CAD|17,443
Korejská republika|won|100|KRW|2,007
Maďarsko|forint|100|HUF|7,610
Malajsie|ringgit|1|MYR|5,554
Mexiko|peso|1|MXN|1,081
MMF|ZPČ|1|XDR|32,441
Norsko|koruna|1|NOK|2,496
Nový Zéland|dolar|1|NZD|15,212
Polsko|zlotý|1|PLN|6,065
Rumunsko|leu|1|RON|5,565
Rusko|rubl|100|RUB|29,811
Singapur|dolar|1|SGD|16,958
Švédsko|koruna|1|SEK|2,602
Švýcarsko|frank|1|CHF|25,162
Thajsko|baht|100|THB|74,009
Turecko|lira|1|TRY|2,908
USA|dolar|1|USD|22,983
Velká Británie|libra|1|GBP|29,737

Ovšem tabulky s reálnými daty mohou být obrovské, zde na konkrétním příkladu může být délka řádku i několik kilobajtů:

Event Desc|En No|Site Name|Licensee Name|Region No|City Name|State Cd|County Name|License No|Agreement State Ind|Docket No|Unit Ind1|Unit Ind2|Unit Ind3|Reactor Type|Nrc Notified By|Ops Officer|Notification Dt|Notification Time|Event Dt|Event Time|Time Zone|Last Updated Dt|Emergency Class|Cfr Cd1|Cfr Descr1|Cfr Cd2|Cfr Descr2|Cfr Cd3|Cfr Descr3|Cfr Cd4|Cfr Descr4|Staff Name1|Org Abbrev1|Staff Name2|Org Abbrev2|Staff Name3|Org Abbrev3|Staff Name4|Org Abbrev4|Staff Name5|Org Abbrev5|Staff Name6|Org Abbrev6|Staff Name7|Org Abbrev7|Staff Name8|Org Abbrev8|Staff Name9|Org Abbrev9|Staff Name10|Org Abbrev10|Scram Code 1|RX CRIT 1|Initial PWR 1|Initial RX Mode1|Current PWR 1|Current RX Mode 1|Scram Code 2|RX CRIT 2|Initial PWR 2|Initial RX Mode 2|Current PWR 2|Current RX Mode 2|Scram Code 3|RX CRIT 3|Initial PWR 3|Initial RX Mode 3|Current PWR 3|Current RX Mode 3|Event Text|
Power Reactor|46521|PILGRIM|ENTERGY NUCLEAR|1|PLYMOUTH|MA|PLYMOUTH||Y|05000293|1|||[1] GE-3|MERT PROBASCO|MARK ABRAMOVITZ|1/5/2011 00:00:00|09:03|1/5/2011 00:00:00|01:20|EST|3/4/2011 00:00:00|NON EMERGENCY|50.72(b)(3)(v)(D)|ACCIDENT MITIGATION|||||||NEIL PERRY|R1DO|||||||||||||||||||N|Y|100|Power Operation|100|Power Operation|N|N|0||0||N|N|0||0||REACTOR CORE ISOLATION COOLING DECLARED INOPERABLE  "On January 5, 2011, at 0120 hours, with the reactor at 100% thermal power and steady state conditions, Pilgrim Nuclear Power Station (PNSP) declared the Reactor Core Isolation Cooling (RCIC) system inoperable due to the RCIC suction isolation valve from the Torus/Suppression Pool (RCIC-26) failing to go fully closed during planned surveillance testing.  The RCIC-26 is a motor-operated valve (MOV) and its normal position is closed.  The RClC-26 valve is redundant to the RCIC-25 valve, and is not the credited containment isolation valve.  The RCIC-26 valve has a safety function to be (manually) opened during certain event mitigation scenarios requiring a transfer of suction sources from the Condensate Storage Tank (CST) to the Torus.  "Based on the valve failing to fully close during MOV stroke time testing per PNPS Procedure 8.5.5.4, the RCIC system was declared inoperable at 0120 hours and the appropriate LCO was entered.  The RCIC-26 was subsequently returned to a full open position, caution tagged and the RCIC system was declared operable.  The LCO was exited at 0200 hours.  An investigation of the event is underway and continuing.  "This event had no impact on the health and/or safety of the public.  "The NRC Resident Inspector is on-site and has been notified.  "This is an 8-hour notification made in accordance with 50.72(b)(3)(v)(D)."  The licensee will notify the State of Massachusetts.   * * * RETRACTION FROM JOSEPH LYNCH TO JOHN KNOKE AT 1946 EST ON 3/4/11 * * *  "Event Notification 46521 was conservatively made to ensure that the Eight-Hour Non-Emergency reporting requirements of 10 CFR 50.72 were satisfied pending the evaluation of RCIC System operability.  "On 01/05/11, at 0120 hours the RCIC System was declared inoperable due to uncertainty of RCIC System Operability when the Torus/Suppression Pool Suction Valve (RCIC-26) failed to go fully closed during planned surveillance testing. The valve was restored to the full open position and the valve was declared operable based on capability to meet the required safety function to fully open when RCIC pump suction from the suppression pool is required.  "The apparent cause evaluation concluded that valve failure was the result of high relay contact resistance in the closing control circuit components of the valve breaker. This failure prevented the valve from fully closing but had no affect on capability to open the valve. Surveillance testing verified that capability to open the valve was not affected.  "Corrective action was completed to clean or replace the control circuit relay contacts. Post work testing confirmed capability to open and close the valve. An extent of condition for similar breaker control circuit components was also performed. All relevant technical information is documented in the corrective action system.  "The failure observed did not affect the valve's required safety function and did not impact RCIC System operability. Thus there was no impact on nuclear safety. This event is not reportable pursuant to 10 CFR 50.72(b)(3)(v)(D) .  "Event Number 46521, made on 01/05/2011, is being retracted."  The licensee has notified the NRC Resident Inspector.  Notified R1DO (Anthony Dimitriadis)|
Agreement State|46528|WISCONSIN RADIATION PROTECTION|SAINT NICHOLAS HOSPITAL|3|SHEBOYGEN|WI||117-1302-01|Y||||||CHRIS TIMMERMAN|JOE O'HARA|1/10/2011 00:00:00|14:29|1/10/2011 00:00:00||CST|3/1/2011 00:00:00|NON EMERGENCY||AGREEMENT STATE|||||||MICHAEL KUNOWSKI|R3DO|JIM LUEHMAN|FSME|||||||||||||||||N|N|0||0||N|N|0||0||N|N|0||0||AGREEMENT STATE REPORT - NUMEROUS MEDICAL EVENTS FROM PROSTATE BRACHYTHERAPY  The following was received from the state via fax;  "In July 2010, the Wisconsin Department of Health Services (DHS) sent out an Information Notice to all licensees who perform prostate brachytherapy and asked them to perform a comprehensive review of all prostate brachytherapy cases to determine whether any medical events had occurred.  On January 10, 2011, the licensee's Radiation Safely Officer reported the identification of five medical events involving permanent implants of I-125 for prostate brachytherapy where the total dose delivered differs from the prescribed dose by 20% or more. The licensee is identifying a medical event of any case where D90<135 Gy or D90>195 Gy for patients who receive seed implants only. [D90 is a recognized value in the regulatory guidelines and means a dose of 90% to the prostate.  Anything outside of the D90 value is considered to be a medical event.] The licensee performed a comprehensive review of all 44 prostate implants performed since August 2003. The licensee's five medical events include one overdose to the prostate and four underdoses to the prostate.  All were patients who received seed implants only. No medical events were identified involving doses to other organs or tissue above 0.50 Sv and 50% more than the expected dose. The licensee has notified the referring physicians and will not be notifying the affected patients per DHS 157.72(1)(e).  "Overdoses (medical event criteria used: D90>195 Gy): 11/13/2008: 199.15 Gy  "Underdoses (medical event criteria used: D90<135 Gy): 2/9/2007: 100.20 Gy;  11/12/2007: 127.34 Gy;  6/16/2008: 130.12 Gy; and  7/13/2010: 116.16 Gy"   * * * UPDATE FROM CHRIS TIMMERMAN TO JOHN KNOKE AT 1212 EST ON 2/1/11 * * *  "This is an update to Event Notification 46528. The licensee recently performed post-implant dosimetry on seven prostate brachytherapy patients whose post-implant dosimetry had never been performed. Evaluation of these seven implants prompted the licensee to report two additional medical events. The medical events involved permanent implants of l-125 for prostate brachytherapy where the total dose delivered to the prostate differs from the prescribed dose by 20% or more. The licensee is in the process of notifying the affected patients and referring physicians.  "Underdoses (medical event criteria used: D90<135 Gy):  8/22/2005: 102.89 Gy; and 5/8/2006: 126.24 Gy;  "DHS will send a special inspection team to determine the root cause(s) of these medical events."  WI Event Report ID No.: WI 110001 Update    Notified FSME(Angela McIntire) and R3DO (Richard Skokowski)   A Medical Event may indicate potential problems in a medical facility's use of radioactive materials.  It does not necessarily result in harm to the patient.  * RETRACTION FROM MEGAN SHOBER TO JOHN SHOEMAKER VIA FACSIMLE AT 1355 EST ON 3/1/11 *  "This is a second update to Event Notification 46528. The licensee is retracting an overdose previously reported for a patient who received a permanent prostate implant on November 13, 2008.  During a reactive inspection conducted on February 18, 2011, DHS inspectors identified that post-implant dosimetry of prostate brachytherapy implants had not been performed appropriately.  Specifically, the licensee's former physics consultant generated post-plans that were not based on the number of I-125 seeds actually implanted in the patients.  For the patient in question, the original post-plan was based on an implant of 98 seeds; however, only 76 seeds were actually implanted. The licensee's current physicist generated a new post-plan using the correct number of I-125 seeds and observed a corresponding reduction in dose delivered.  The new D90 value for this patient does not meet the licensee's medical event criteria."  WI Event Report ID No.: WI 110002,  2nd Update    Notified FSME(McIntosh) and R3DO (Dickson)|
Power Reactor|46548|SAINT LUCIE|FLORIDA POWER & LIGHT CO.|2|FT. PIERCE|FL|ST LUCIE||Y|05000335|1|2||[1] CE,[2] CE|BRAD BISHOP|HOWIE CROUCH|1/18/2011 00:00:00|10:48|1/19/2011 00:00:00|02:00|EST|3/24/2011 00:00:00|NON EMERGENCY|50.72(b)(3)(xiii)|LOSS COMM/ASMT/RESPONSE|||||||MARVIN SYKES|R2DO|||||||||||||||||||N|Y|100|Power Operation|100|Power Operation|N|N|0|Refueling|0|Refueling|N|N|0||0||EMERGENCY RESPONSE DATA ACQUISITION AND DISPLAY SYSTEM (ERDADS) REMOVED FROM SERVICE FOR MAINTENANCE  "On 1/19/11, St Lucie Unit 1 and Unit 2 will lose the computer trains associated with Emergency Response Data Acquisition and Display System (ERDADS). Unit 1 will be removed for corrective maintenance, and Unit 2 will be removed for system modification. It is expected that Unit 1 will be restored by 1/21/11, and Unit 2 will be returned to a functional status prior to core reload and fully operational by March 20, 2011. Further, neither Unit 1 nor Unit 2 ERDADS will be removed from service until Unit 2 has defueled (currently scheduled for 1/19/2011 at 0200). This is an advance notification of a planned loss of emergency assessment capability, which will be reportable under 10CFR50.72(b)(3)(xiii). Other means to monitor critical data exists. Notification will be made when each unit is restored to available status."  The licensee has notified the NRC Resident Inspector.  * * * UPDATE FROM REESE KILIAN TO HOWIE CROUCH @ 0952 EST ON 1/19/11 * * *  "Unit 1 and 2 ERDADS have been removed from service at 0955 [EST]."  The licensee has notified the NRC Resident Inspector.  * * * UPDATE FROM TIMOTHY KUDO TO JOHN SHOEMAKER @ 1527 EST ON 01/20/11 * * *   Unit 1 ERDADS has been returned to available status as of 1520 EST on 01/20/11.  Unit 2 ERDADS remains out of service.  The licensee has notified the NRC Resident Inspector.  * * * UPDATE FROM BISHOP TO HUFFMAN AT 2013 EDT ON 3/24/11 * * *  Unit 1 and Unit 2 ERDADS have both been restored to service as of 1600 EDT on 3/24/11.    The licensee has notified the NRC Resident Inspector.  R2DO (Rich) notified.|
Power Reactor|46562|KEWAUNEE|NUCLEAR MANAGEMENT COMPANY|3|KEWAUNEE|WI|KEWAUNEE||Y|05000305|1|||[1] W-2-LP|MIKE TERRY|JOHN KNOKE|1/21/2011 00:00:00|22:25|1/21/2011 00:00:00|15:39|CST|3/22/2011 00:00:00|NON EMERGENCY|50.72(b)(3)(ii)(B)|UNANALYZED CONDITION|50.72(b)(3)(v)(B)|POT RHR INOP|50.72(b)(3)(v)(D)|ACCIDENT MITIGATION|||TAMARA BLOOMER|R3DO|||||||||||||||||||N|Y|100|Power Operation|100|Power Operation|N|N|0||0||N|N|0||0||STEAM EXCLUSION DOOR DECLARED INOPERABLE  "On 1/21/2011 at 1539 CST, the NRC Resident Inspector informed the Control Room that the lower Cane bolt was disengaged on Steam Exclusion Door 3, between Emergency Diesel Generator Room B and the Cardox Room. While the Cane bolt was not engaged, the barrier was Non-Functional and, in accordance with TRM 3.0.9, all equipment supported by that steam exclusion barrier was immediately declared inoperable. This included both Emergency Diesel Generators A & 8, safety-related 4160 V Busses 5 & 6, Service Water Trains A & B, and safety-related 480 V Busses 51, 52, 61. & 62. In addition, with Service Water inoperable, the following equipment was also inoperable in accordance with TRM 3.3.1: Component Cooling Trains A & B, Safety Injection Trains A & B, Residual Heat Removal Trains A & B, Containment Spray and Cooling Trains A & B, Auxiliary Feedwater Pumps A & B, and the Turbine Driven Auxiliary Feedwater Pump. With all three AFW pumps inoperable. TS 3 A.b.2 was entered to immediately initiate action to restore one AFW Train to operable status and suspend all LCOs requiring mode changes until one AFW Train is restored to operable status.  "Steam Exclusion Door 3 was properly secured at 1545 CST on 1/21/2011, and LCO 3.0.c and TS 3 A.b.2 were exited at that time. All equipment affected by the steam exclusion barrier is operable.  "This is reportable under 10 CFR 50.72 (b)(3)(v)(B), 'Any event or condition that at the time of discovery could have prevented the fulfillment of a safety function,' and under 10 CFR 50.72(b)(3)(ii)(B) 'any event or condition that results in the nuclear plant being in an unanalyzed condition that significantly degrades plant safety.'"  The licensee notified the NRC Resident Inspector.   * * * RETRACTION FROM CRAIG J. NEUSER TO DONALD NORWOOD AT 1427 EDT ON 3/22/2011 * * *  "Retraction of EN #46562 Non-Functional Steam Exclusion Door.  "On January 21, 2011, EN #46562 provided notification that both trains of ESF equipment (e.g., SI, RHR, ICS, etc ) were inoperable following discovery that the lower cane bolt was disengaged on steam exclusion Door 3, between emergency diesel generator Room B and an adjacent equipment room in the turbine building.  With the lower cane bolt disengaged, the steam exclusion barrier was considered non-functional.  "A subsequent engineering evaluation determined that the Door 3 lower cane bolt was not required for Door 3 to fulfill its function as a steam exclusion barrier.  The previously reported condition would not have resulted in an environment that would have adversely impacted the equipment protected by Door 3.  Therefore, the door remained functional and the supported ESF equipment remained operable.  Consequently, this condition did not meet the reportability criteria in 10CFR50.72.  "As a result, the notification made on January 21, 2011, in EN #46562 is hereby retracted.    "The NRC Senior Resident Inspector has been notified."  Notified R3DO(Cameron).|

3. Instalace nástroje q

Poslední verzi zdrojových kódů nástroje q naleznete na adrese https://github.com/harelba/q/ar­chive/2.0.19.zip. Po rozbalení ZIP archivu se instalace spustí příkazem make, jenž využívá cíle definované v souboru Makefile. Interně se pro instalaci závislostí používá pip. Po instalaci se provede i otestování (to lze ovšem přeskočit):

$ sudo make
 
  Using cached https://files.pythonhosted.org/packages/34/a6/49e2849a0e5464e1b5d621f63bc8453066f0f367bb3b744a33fca0bc1ddd/flake8-3.6.0-py2.py3-none-any.whl
Requirement already satisfied: six>=1.10.0 in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: pluggy<1.0,>=0.12 in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: py>=1.5.0 in /usr/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: importlib-metadata>=0.12 in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Collecting atomicwrites>=1.0 (from pytest==4.6.2->-r test-requirements.txt (line1))
  Using cached https://files.pythonhosted.org/packages/2c/a0/da5f49008ec6e9a658dbf5d7310a4debd397bce0b4db03cf8a410066bb87/atomicwrites-1.4.0-py2.py3-none-any.whl
Requirement already satisfied: packaging in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: wcwidth in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: attrs>=17.4.0 in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: more-itertools>=4.0.0; python_version > "2.7" in /home/ptisnovs/.local/lib/python3.6/site-packages (from pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: pyflakes<2.1.0,>=2.0.0 in /home/ptisnovs/.local/lib/python3.6/site-packages (from flake8==3.6.0->-r test-requirements.txt (line 2))
Collecting pycodestyle<2.5.0,>=2.4.0 (from flake8==3.6.0->-r test-requirements.txt (line 2))
  Using cached https://files.pythonhosted.org/packages/e5/c6/ce130213489969aa58610042dff1d908c25c731c9575af6935c2dfad03aa/pycodestyle-2.4.0-py2.py3-none-any.whl
Requirement already satisfied: mccabe<0.7.0,>=0.6.0 in /usr/lib/python3.6/site-packages (from flake8==3.6.0->-r test-requirements.txt (line 2))
Requirement already satisfied: setuptools>=30 in /usr/lib/python3.6/site-packages (from flake8==3.6.0->-r test-requirements.txt (line 2))
Requirement already satisfied: zipp>=0.5 in /home/ptisnovs/.local/lib/python3.6/site-packages (from importlib-metadata>=0.12->pytest==4.6.2->-r test-requirements.txt (line 1))
Requirement already satisfied: pyparsing>=2.0.2 in /usr/lib/python3.6/site-packages (from packaging->pytest==4.6.2->-r test-requirements.txt (line 1))
Installing collected packages: atomicwrites, pytest, pycodestyle, flake8
  Found existing installation: pytest 5.4.2
    Uninstalling pytest-5.4.2:
      Successfully uninstalled pytest-5.4.2
  Found existing installation: pycodestyle 2.3.1
    Uninstalling pycodestyle-2.3.1:
      Successfully uninstalled pycodestyle-2.3.1
  Found existing installation: flake8 3.5.0
    Uninstalling flake8-3.5.0:
      Successfully uninstalled flake8-3.5.0
Successfully installed atomicwrites-1.4.0 flake8-3.6.0 pycodestyle-2.4.0 pytest-4.6.2

pip3 install --user -e .
Obtaining file:///home/ptisnovs/temp/q-2.0.19
Collecting six==1.11.0 (from q==2.0.19)
  Downloading https://files.pythonhosted.org/packages/67/4b/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90a2d863fe3056e86a/six-1.11.0-py2.py3-none-any.whl
Installing collected packages: six, q
  Found existing installation: six 1.12.0
    Uninstalling six-1.12.0:
      Successfully uninstalled six-1.12.0
  Running setup.py develop for q
Successfully installed q six-1.11.0
Poznámka: můžete samozřejmě použít i balíčky s nástrojem q dostupné přímo v repositářích vaší Linuxové distribuce, ale mnohdy se (zejména u enterprise distribucí) jedná o velmi staré verze.

4. Lokální instalace, použití pip3

Ve starších instalacích Linuxu je nástroj pip použit společně s Pythonem 2 a nikoli s Pythonem 3. Pokud na takových systémech budete chtít použít Python 3, je nutná nepatrná úprava souboru Makefile. Dále je možné Makefile nepatrně upravit takovým způsobem, aby se instalace provedla lokálně pro právě přihlášeného uživatele, což znamená úpravu příkazu pip install popř. pip3 install. Při lokální instalaci do adresáře ~/.local není nutné mít práva superuživatele a navíc lze nainstalovat i novější verzi, která nebude kolidovat s verzí systémovou:

--- Makefile    2020-09-22 16:20:00.000000001 +0200
+++ /home/ptisnovs/temp/q-2.0.19-/Makefile      2020-10-09 16:39:25.392295392 +0200
@@ -14,8 +14,8 @@
 
 dep: ## Install the dependent libraries.
 
-       pip install -r test-requirements.txt
-       pip install -e .
+       pip3 install --user -r test-requirements.txt
+       pip3 install --user -e .
 
 lint: dep ## Run lint validations.
 
@@ -28,10 +28,10 @@
        ## py.test -rs -c pytest.ini -s -v q/tests/suite.py --rootdir .
 
 release: ## Run release
-       pip install py-ci
+       pip3 install --user py-ci
        pyci release --no-wheel-publish --wheel-universal
 
 local-release:
-       pip install py-ci
+       pip3 install --user py-ci
        ./do-manual-release.sh

5. Základní kontrola instalace nástroje q

Dále provedeme jednoduchou kontrolu, zda instalace nástroje q proběhla korektně. Mělo by stačit si vyhledat umístění spustitelného souboru q:

$ whereis q
 
q: /home/ptisnovs/.local/bin/q

Pokusme se nástroj q spustit, a to prozatím bez uvedení přepínačů:

$ q --version
 
q version 2.0.19
Python: 3.6.6 (default, Jul 19 2018, 16:29:00) // [GCC 7.3.1 20180303 (Red Hat 7.3.1-5)]
Copyright (C) 2012-2020 Harel Ben-Attia (harelba@gmail.com, @harelba on twitter)
http://harelba.github.io/q/
Poznámka: povšimněte si, že je použit zastaralý Python 3.6.6 namísto novějších verzí 3.8 nebo 3.9. To však možnosti q žádným způsobem neomezuje.

K dispozici je pochopitelně i vestavěná nápověda:

$ q --help
 
Usage:
        q allows performing SQL-like statements on tabular text data.
 
        Its purpose is to bring SQL expressive power to manipulating text data using the Linux command line.
 
        Basic usage is q "<sql like query>" where table names are just regular file names (Use - to read from standard input)
            When the input contains a header row, use -H, and column names will be set according to the header row content. If there isn't a header row, then columns will automatically be named c1..cN.
 
        Column types are detected automatically. Use -A in order to see the column name/type analysis.
 
        Delimiter can be set using the -d (or -t) option. Output delimiter can be set using -D
 
        All sqlite3 SQL constructs are supported.
 
        Examples:
 
              Example 1: ls -ltrd * | q "select c1,count(1) from - group by c1"
            This example would print a count of each unique permission string in the current folder.
 
          Example 2: seq 1 1000 | q "select avg(c1),sum(c1) from -"
            This example would provide the average and the sum of the numbers in the range 1 to 1000
 
          Example 3: sudo find /tmp -ls | q "select c5,c6,sum(c7)/1024.0/1024 as total from - group by c5,c6 order by total desc"
            This example will output the total size in MB per user+group in the /tmp subtree
 
 
            See the help or https://github.com/harelba/q/ for more details.
 
 
Options:
  -h, --help            show this help message and exit
  -v, --version         Print version
  -V, --verbose         Print debug info in case of problems
  -S SAVE_DB_TO_DISK_FILENAME, --save-db-to-disk=SAVE_DB_TO_DISK_FILENAME
                        Save database to an sqlite database file
  --save-db-to-disk-method=SAVE_DB_TO_DISK_METHOD
                        Method to use to save db to disk. 'standard' does not
                        require any deps, 'fast' currenty requires manually
                        running `pip install sqlitebck` on your python
                        installation. Once packing issues are solved, the fast
                        method will be the default.
 
  Input Data Options:
    -H, --skip-header   Skip header row. This has been changed from earlier
                        version - Only one header row is supported, and the
                        header row is used for column naming
    -d DELIMITER, --delimiter=DELIMITER
                        Field delimiter. If none specified, then space is used
                        as the delimiter.
    -p, --pipe-delimited
                        Same as -d '|'. Added for convenience and readability
    -t, --tab-delimited
                        Same as -d <tab>. Just a shorthand for handling
                        standard tab delimited file You can use $'\t' if you
                        want (this is how Linux expects to provide tabs in the
                        command line
    -e ENCODING, --encoding=ENCODING
                        Input file encoding. Defaults to UTF-8. set to none
                        for not setting any encoding - faster, but at your own
                        risk...
    -z, --gzipped       Data is gzipped. Useful for reading from stdin. For
                        files, .gz means automatic gunzipping
    -A, --analyze-only  Analyze sample input and provide information about
                        data types
    -m MODE, --mode=MODE
                        Data parsing mode. fluffy, relaxed and strict. In
                        strict mode, the -c column-count parameter must be
                        supplied as well
    -c COLUMN_COUNT, --column-count=COLUMN_COUNT
                        Specific column count when using relaxed or strict
                        mode
    -k, --keep-leading-whitespace
                        Keep leading whitespace in values. Default behavior
                        strips leading whitespace off values, in order to
                        provide out-of-the-box usability for simple use cases.
                        If you need to preserve whitespace, use this flag.
    --disable-double-double-quoting
                        Disable support for double double-quoting for escaping
                        the double quote character. By default, you can use ""
                        inside double quoted fields to escape double quotes.
                        Mainly for backward compatibility.
    --disable-escaped-double-quoting
                        Disable support for escaped double-quoting for
                        escaping the double quote character. By default, you
                        can use \" inside double quoted fields to escape
                        double quotes. Mainly for backward compatibility.
    --as-text           Don't detect column types - All columns will be
                        treated as text columns
    -w INPUT_QUOTING_MODE, --input-quoting-mode=INPUT_QUOTING_MODE
                        Input quoting mode. Possible values are all, minimal
                        and none. Note the slightly misleading parameter name,
                        and see the matching -W parameter for output quoting.
    -M MAX_COLUMN_LENGTH_LIMIT, --max-column-length-limit=MAX_COLUMN_LENGTH_LIMIT
                        Sets the maximum column length.
    -U, --with-universal-newlines
                        Expect universal newlines in the data. Limitation: -U
                        works only with regular files for now, stdin or .gz
                        files are not supported yet.
 
  Output Options:
    -D OUTPUT_DELIMITER, --output-delimiter=OUTPUT_DELIMITER
                        Field delimiter for output. If none specified, then
                        the -d delimiter is used if present, or space if no
                        delimiter is specified
    -P, --pipe-delimited-output
                        Same as -D '|'. Added for convenience and readability.
    -T, --tab-delimited-output
                        Same as -D <tab>. Just a shorthand for outputting tab
                        delimited output. You can use -D $'\t' if you want.
    -O, --output-header
                        Output header line. Output column-names are determined
                        from the query itself. Use column aliases in order to
                        set your column names in the query. For example,
                        'select name FirstName,value1/value2 MyCalculation
                        from ...'. This can be used even if there was no
                        header in the input.
    -b, --beautify      Beautify output according to actual values. Might be
                        slow...
    -f FORMATTING, --formatting=FORMATTING
                        Output-level formatting, in the format X=fmt,Y=fmt
                        etc, where X,Y are output column numbers (e.g. 1 for
                        first SELECT column etc.
    -E OUTPUT_ENCODING, --output-encoding=OUTPUT_ENCODING
                        Output encoding. Defaults to 'none', leading to
                        selecting the system/terminal encoding
    -W OUTPUT_QUOTING_MODE, --output-quoting-mode=OUTPUT_QUOTING_MODE
                        Output quoting mode. Possible values are all, minimal,
                        nonnumeric and none. Note the slightly misleading
                        parameter name, and see the matching -w parameter for
                        input quoting.
    -L, --list-user-functions
                        List all user functions
 
  Query Related Options:
    -q QUERY_FILENAME, --query-filename=QUERY_FILENAME
                        Read query from the provided filename instead of the
                        command line, possibly using the provided query
                        encoding (using -Q).
    -Q QUERY_ENCODING, --query-encoding=QUERY_ENCODING
                        query text encoding. Experimental. Please send your
                        feedback on this

6. Tabulky ve formátu TSV použité v příkladech

V navazujících kapitolách si uvedeme několik příkladů SQL dotazů, které budou používat jednoduchou tabulku uloženou v souboru tiobe.tsv. Jak již koncovka souboru naznačuje, jedná se o tabulku uloženou ve formát TSV, jejíž obsah může vypadat následovně. Tento soubor je dostupný na adrese https://github.com/tisnik/r-examples/blob/master/data/tiobe.tsv:

v         v         v         v                  v         v
Sep 2020  Sep 2019  Change    Language           Ratings   Changep
1         2         change    C                  15.95%    +0.74%
2         1         change    Java               13.48%    -3.18%
3         3                   Python             10.47%    +0.59%
4         4                   C++                7.11%     +1.48%
5         5                   C#                 4.58%     +1.18%
6         6                   Visual Basic       4.12%     +0.83%
7         7                   JavaScript         2.54%     +0.41%
8         9         change    PHP                2.49%     +0.62%
9         19        change    R                  2.37%     +1.33%
10        8         change    SQL                1.76%     -0.19%
11        14        change    Go                 1.46%     +0.24%
12        16        change    Swift              1.38%     +0.28%
13        20        change    Perl               1.30%     +0.26%
14        12        change    Assembly language  1.30%     -0.08%
15        15                  Ruby               1.24%     +0.03%
16        18        change    MATLAB             1.10%     +0.04%
17        11        change    Groovy             0.99%     -0.52%
18        33        change    Rust               0.92%     +0.55%
19        10        change    Objective-C        0.85%     -0.99%
20        24        change    Dart               0.77%     +0.13%
^         ^         ^         ^                  ^         ^
Poznámka: první a poslední řádek se značkami v a ^ není součástí tabulky. Pouze naznačuje umístění tabelačních zarážek při zobrazení.

Výše uvedená tabulka je schválně vytvořena takovým způsobem, aby se ukázalo, jak nástroj q umí (resp. neumí) pracovat s hodnotami typu „15.95%“ apod. Používat budeme ještě jeden datový soubor, z něhož budou odstraněny znaky procent a sloupečky tedy budou obsahovat čistě numerické hodnoty. Tento soubor je dostupný na adrese https://github.com/tisnik/r-examples/blob/master/data/tiobe2.csv a jeho obsah (po expanzi TABů) vypadá následovně:

v         v         v         v                  v         v
Sep 2020  Sep 2019  Change    Language           Ratings   Changep
1         2         change    C                  15.95     +0.74
2         1         change    Java               13.48     -3.18
3         3                   Python             10.47     +0.59
4         4                   C++                7.11      +1.48
5         5                   C#                 4.58      +1.18
6         6                   Visual Basic       4.12      +0.83
7         7                   JavaScript         2.54      +0.41
8         9         change    PHP                2.49      +0.62
9         19        change    R                  2.37      +1.33
10        8         change    SQL                1.76      -0.19
11        14        change    Go                 1.46      +0.24
12        16        change    Swift              1.38      +0.28
13        20        change    Perl               1.30      +0.26
14        12        change    Assembly language  1.30      -0.08
15        15                  Ruby               1.24      +0.03
16        18        change    MATLAB             1.10      +0.04
17        11        change    Groovy             0.99      -0.52
18        33        change    Rust               0.92      +0.55
19        10        change    Objective-C        0.85      -0.99
20        24        change    Dart               0.77      +0.13
^         ^         ^         ^                  ^         ^

7. Jednoduché dotazy nad tabulkou uloženou ve formátu TSV

Nyní se konečně dostáváme k použití nástroje q. Víme již, že by mělo být možné nad daty uloženými v TSV tvořit SQL dotazy. Pokusme se tedy zjistit počet prvků uložených v tabulce reprezentované souborem tiobe.tsv:

$ q "select count(*) from tiobe.tsv"

Výsledek pravděpodobně nebude odpovídat očekávání:

Warning - There seems to be header line in the file, but -H has not been specified. All fields will be detected as text fields, and the header line will appear as part of the data
21

Vidíme, že se vypsala hodnota 21, což by mělo představovat počet záznamů uložených v tabulce. Současně se vypsalo varování o tom, že nástroj q s velkou pravděpodobností nalezl první řádek s názvy sloupců. Tento řádek je nutné zpracovat zvláštním způsobem a k tomu slouží přepínač -H. Upravme tedy zavolání q:

$ q -H "select count(*) from tiobe.tsv"

Nyní by se již varování nemělo objevit a vypsat by se měla očekávaná hodnota:

20

Podobně problematické bude vypsání obsahu tabulky, pokud neuvedeme přepínač -H:

$ q "select * from tiobe.tsv"

Výsledek zdánlivě vypadá v pořádku, ovšem názvy sloupců nejsou skutečnými názvy sloupců, ale běžná data (první záznam tabulky):

Warning - There seems to be header line in the file, but -H has not been specified. All fields will be detected as text fields, and the header line will appear as part of the data
Sep 2020  Sep 2019  Change  Language              Ratings  Changep
1         2         change  C                     15.95%   +0.74%
2         1         change  Java                  13.48%   -3.18%
3         3                 Python                10.47%   +0.59%
4         4                 C++                   7.11%    +1.48%
5         5                 C#                    4.58%    +1.18%
6         6                 Visual Basic          4.12%    +0.83%
7         7                 JavaScript            2.54%    +0.41%
8         9         change  PHP                   2.49%    +0.62%
9         19        change  R                     2.37%    +1.33%
10        8         change  SQL                   1.76%    -0.19%
11        14        change  Go                    1.46%    +0.24%
12        16        change  Swift                 1.38%    +0.28%
13        20        change  Perl                  1.30%    +0.26%
14        12        change  Assembly language     1.30%    -0.08%
15        15                Ruby                  1.24%    +0.03%
16        18        change  MATLAB                1.10%    +0.04%
17        11        change  Groovy                0.99%    -0.52%
18        33        change  Rust                  0.92%    +0.55%
19        10        change  Objective-C           0.85%    -0.99%
20        24        change  Dart                  0.77%    +0.13%

Upravená korektní varianta dotazu:

$ q -H "select * from tiobe.tsv"

S výsledky:

Sep 2020  Sep 2019  Change  Language              Ratings  Changep
1         2         change  C                     15.95%   +0.74%
2         1         change  Java                  13.48%   -3.18%
3         3                 Python                10.47%   +0.59%
4         4                 C++                   7.11%    +1.48%
5         5                 C#                    4.58%    +1.18%
6         6                 Visual Basic          4.12%    +0.83%
7         7                 JavaScript            2.54%    +0.41%
8         9         change  PHP                   2.49%    +0.62%
9         19        change  R                     2.37%    +1.33%
10        8         change  SQL                   1.76%    -0.19%
11        14        change  Go                    1.46%    +0.24%
12        16        change  Swift                 1.38%    +0.28%
13        20        change  Perl                  1.30%    +0.26%
14        12        change  Assembly language     1.30%    -0.08%
15        15                Ruby                  1.24%    +0.03%
16        18        change  MATLAB                1.10%    +0.04%
17        11        change  Groovy                0.99%    -0.52%
18        33        change  Rust                  0.92%    +0.55%
19        10        change  Objective-C           0.85%    -0.99%
20        24        change  Dart                  0.77%    +0.13%

8. Použití klauzule order by

Jména sloupců lze použít v SQL dotazech, například v klauzuli order by. Pokusme se nyní tuto klauzuli použít:

$ q -H "select * from tiobe.tsv order by changep"
 
query error: no such column: changep
Warning - There seems to be a "no such column" error, and -H (header line) exists. Please make sure that you are using the column names from the header line and not the default (cXX) column names. Another issue might be that the file contains a BOM. Files that are encoded with UTF8 and contain a BOM can be read by specifying `-e utf-9-sig` in the command line. Support for non-UTF8 encoding will be provided in the future.

Vidíme, že došlo k chybě. Doposud byl totiž vstupní soubor načítán takovým způsobem, jakoby obsahoval na každém řádku pouze jediný záznam. Musíme tedy explicitně specifikovat, že jednotlivé položky záznamu jsou odděleny znakem TAB. K tomu slouží přepínač -t:

$ q -H -t "select * from tiobe.tsv order by changep"
 
15      15              Ruby               1.24%   +0.03%
16      18      change  MATLAB             1.10%   +0.04%
20      24      change  Dart               0.77%   +0.13%
11      14      change  Go                 1.46%   +0.24%
13      20      change  Perl               1.30%   +0.26%
12      16      change  Swift              1.38%   +0.28%
7       7               JavaScript         2.54%   +0.41%
18      33      change  Rust               0.92%   +0.55%
3       3               Python             10.47%  +0.59%
8       9       change  PHP                2.49%   +0.62%
1       2       change  C                  15.95%  +0.74%
6       6               Visual Basic       4.12%   +0.83%
5       5               C#                 4.58%   +1.18%
9       19      change  R                  2.37%   +1.33%
4       4               C++                7.11%   +1.48%
14      12      change  Assembly language  1.30%   -0.08%
10      8       change  SQL                1.76%   -0.19%
17      11      change  Groovy             0.99%   -0.52%
19      10      change  Objective-C        0.85%   -0.99%
2       1       change  Java               13.48%  -3.18%
Poznámka: povšimněte si, že k setřídění došlo tak, jakoby sloupec obsahovat textové hodnoty a nikoli hodnoty numerické.

Vybrat můžeme i konkrétní sloupce ze vstupní tabulky:

$ q -H -t "select Language, Ratings from tiobe.tsv order by changep"
 
Ruby              1.24%
MATLAB            1.10%
Dart              0.77%
Go                1.46%
Perl              1.30%
Swift             1.38%
JavaScript        2.54%
Rust              0.92%
Python            10.47%
PHP               2.49%
C                 15.95%
Visual Basic      4.12%
C#                4.58%
R                 2.37%
C++               7.11%
Assembly language 1.30%
SQL               1.76%
Groovy            0.99%
Objective-C       0.85%
Java              13.48%

9. Klauzule order by a sloupce s numerickými hodnotami

V případě, že sloupec obsahuje (kromě prvního řádku s hlavičkami) pouze numerické hodnoty, budou tyto hodnoty setříděny klauzulí order by korektním způsobem:

$ q -H -t "select Language, Ratings from tiobe2.tsv order by changep"
 
Java               13.48
Objective-C         0.85
Groovy              0.99
SQL                 1.76
Assembly language   1.3
Ruby                1.24
MATLAB              1.1
Dart                0.77
Go                  1.46
Perl                1.3
Swift               1.38
JavaScript          2.54
Rust                0.92
Python             10.47
PHP                 2.49
C                  15.95
Visual Basic        4.12
C#                  4.58
R                   2.37
C++                 7.11

Do výstupu lze vložit i úvodní řádek s hlavičkami sloupců:

$ q -H -t -O "select Language, Ratings from tiobe2.tsv order by changep"
 
Language        Ratings
Java               13.48
Objective-C         0.85
Groovy              0.99
SQL                 1.76
Assembly language   1.3
Ruby                1.24
MATLAB              1.1
Dart                0.77
Go                  1.46
Perl                1.3
Swift               1.38
JavaScript          2.54
Rust                0.92
Python             10.47
PHP                 2.49
C                  15.95
Visual Basic        4.12
C#                  4.58
R                   2.37
C++                 7.11

10. Omezení počtu řádků ve výsledku dotazu klauzulí limit

Počet řádků na výstupu je možné omezit klauzulí limit XX popř. doplněnou o modifikaci offset YY. Následující dotaz vypíše pouze prvních deset řádků z tabulky (omezení na dva sloupce):

$ q -H -t "select Language, Ratings from tiobe.tsv limit 10"
 
C               15.95%
Java            13.48%
Python          10.47%
C++             7.11%
C#              4.58%
Visual Basic    4.12%
JavaScript      2.54%
PHP             2.49%
R               2.37%
SQL             1.76%

Takový dotaz lze spojit s ostatními klauzulemi, například:

$ q -H -t -O "select Language, Ratings from tiobe2.tsv order by Ratings desc limit 10"
 
Language        Ratings
C               15.95
Java            13.48
Python          10.47
C++              7.11
C#               4.58
Visual Basic     4.12
JavaScript       2.54
PHP              2.49
R                2.37
SQL              1.76

Přidání modifikátoru offset YY:

$ q -H -t -O "select Language, Ratings from tiobe2.tsv order by Ratings desc limit 10 offset 10"
 
Language        Ratings
Go                1.46
Swift             1.38
Perl              1.3
Assembly language 1.3
Ruby              1.24
MATLAB            1.1
Groovy            0.99
Rust              0.92
Objective-C       0.85
Dart              0.77

11. Podmínka specifikovaná klauzulí where

Nástroj q pochopitelně umožňuje zadání podmínky či podmínek klauzulí where. Podívejme se na příklad, v němž zobrazíme pouze ty jazyky, jejichž popularita přesahuje hranici pěti procent:

$ q -H -t "select Language, Ratings from tiobe2.tsv where Ratings > 5 order by Ratings desc"
 
C       15.95
Java    13.48
Python  10.47
C++     7.11

Podobně lze zobrazit jazyky, které jsou v žebříčku méně populární:

$ q -H -t "select Language, Ratings from tiobe2.tsv where Ratings <= 5 order by Ratings desc"
 
C#                4.58
Visual Basic      4.12
JavaScript        2.54
PHP               2.49
R                 2.37
SQL               1.76
Go                1.46
Swift             1.38
Perl              1.3
Assembly language 1.3
Ruby              1.24
MATLAB            1.1
Groovy            0.99
Rust              0.92
Objective-C       0.85
Dart              0.77

Skládání složitějších podmínek s využitím logických spojek:

$ q -H -t "select Language, Ratings from tiobe2.tsv where Ratings > 2 and Language != 'Java' order by Ratings desc"
 
C       15.95
Python  10.47
C++     7.11
C#      4.58
Visual Basic    4.12
JavaScript      2.54
PHP     2.49
R       2.37

Použití operátoru like:

$ q -H -t "select * from tiobe2.tsv where Language like 'C%'"
 
1       2       change  C       15.95   0.74
4       4               C++     7.11    1.48
5       5               C#      4.58    1.18

12. Agregační funkce

Nástroj q podporuje i všech pět základních agregačních funkcí jazyka SQL. Je tedy možné zjistit počet záznamů (odpovídajících podmínce atd.), součet numerických hodnot, jejich průměr, maximální hodnotu i hodnotu minimální. Příklady použití mohou být velmi jednoduché:

$ q -H -t "select count(Ratings) from tiobe2.tsv"
20
 
$ q -H -t "select sum(Ratings) from tiobe2.tsv"
76.17999999999995
 
$ q -H -t "select avg(Ratings) from tiobe2.tsv"
3.8089999999999975
 
$ q -H -t "select max(Ratings) from tiobe2.tsv"
15.95
 
$ q -H -t "select min(Ratings) from tiobe2.tsv"
0.77

Pochopitelně je možné zkombinovat agregační funkci s nějakou podmínkou:

$ q -H -t "select count(Ratings) from tiobe2.tsv where Ratings>5"
4

Podmínka může obsah i operátor like atd.:

$ q -H -t "select count(*) from tiobe2.tsv where Language like 'C%'"
3

13. Tisk výsledků ve formě dále zpracovatelné tabulky

Výsledky dotazů je mnohdy nutné uložit v takové formě, která bude dále zpracovatelná (jako další tabulka). Volba výstupního formátu se provádí především přepínači -P, -T zkombinované s přepínačem -O.

Přepínačem -P se zapíná výstup ve formě údajů oddělených znakem | (pipe):

$ q -H -t -P "select * from tiobe.tsv"
 
1|2|change|C|15.95%|+0.74%
2|1|change|Java|13.48%|-3.18%
3|3||Python|10.47%|+0.59%
4|4||C++|7.11%|+1.48%
5|5||C#|4.58%|+1.18%
6|6||Visual Basic|4.12%|+0.83%
7|7||JavaScript|2.54%|+0.41%
8|9|change|PHP|2.49%|+0.62%
9|19|change|R|2.37%|+1.33%
10|8|change|SQL|1.76%|-0.19%
11|14|change|Go|1.46%|+0.24%
12|16|change|Swift|1.38%|+0.28%
13|20|change|Perl|1.30%|+0.26%
14|12|change|Assembly language|1.30%|-0.08%
15|15||Ruby|1.24%|+0.03%
16|18|change|MATLAB|1.10%|+0.04%
17|11|change|Groovy|0.99%|-0.52%
18|33|change|Rust|0.92%|+0.55%
19|10|change|Objective-C|0.85%|-0.99%
20|24|change|Dart|0.77%|+0.13%

Přidání hlaviček do výstupní tabulky:

$ q -H -t -P -O "select * from tiobe.tsv"
Sep 2020|Sep 2019|Change|Language|Ratings|Changep
1|2|change|C|15.95%|+0.74%
2|1|change|Java|13.48%|-3.18%
3|3||Python|10.47%|+0.59%
4|4||C++|7.11%|+1.48%
5|5||C#|4.58%|+1.18%
6|6||Visual Basic|4.12%|+0.83%
7|7||JavaScript|2.54%|+0.41%
8|9|change|PHP|2.49%|+0.62%
9|19|change|R|2.37%|+1.33%
10|8|change|SQL|1.76%|-0.19%
11|14|change|Go|1.46%|+0.24%
12|16|change|Swift|1.38%|+0.28%
13|20|change|Perl|1.30%|+0.26%
14|12|change|Assembly language|1.30%|-0.08%
15|15||Ruby|1.24%|+0.03%
16|18|change|MATLAB|1.10%|+0.04%
17|11|change|Groovy|0.99%|-0.52%
18|33|change|Rust|0.92%|+0.55%
19|10|change|Objective-C|0.85%|-0.99%
20|24|change|Dart|0.77%|+0.13%

Výstup do formátu, v němž jsou jako oddělovače použity znaky TAB:

$ q -H -t -O "select Language, Ratings from tiobe2.tsv where Ratings > 5 order by Ratings desc"
 
Language        Ratings
C       15.95
Java    13.48
Python  10.47
C++     7.11

Samozřejmě je možné kombinovat přepínače pro výstup s komplikovanějšími dotazy:

$ q -H -t -P "select Language, Ratings from tiobe2.tsv where Ratings > 5 order by Ratings desc"
 
C|15.95
Java|13.48
Python|10.47
C++|7.11

popř.:

$ q -H -O -t -P "select Language, Ratings from tiobe2.tsv where Ratings > 5 order by Ratings desc"
 
Language|Ratings
C|15.95
Java|13.48
Python|10.47
C++|7.11

14. Naformátování výstupu pro zlepšení čtení výsledné tabulky

Pokud má být výsledkem dotazu tabulka, která má být čtena lidmi, může být výhodné použít přepínač -b, který zajistí, že se hodnoty ve sloupci umístí pod sebe. Zpracování je ovšem zpomaleno, protože je nutné nejdříve spočítat šířky sloupců:

$ q -H -t -P -b "select Language, Ratings from tiobe2.tsv where Ratings > 5 order by Ratings desc"
 
C     |15.95
Java  |13.48
Python|10.47
C++   |7.11

Další příklad, tentokrát pro tabulku bez hlaviček:

$ q -H -t -P -b "select * from tiobe2.tsv"
 
1 |2 |change|C                |15.95|0.74
2 |1 |change|Java             |13.48|-3.18
3 |3 |      |Python           |10.47|0.59
4 |4 |      |C++              |7.11 |1.48
5 |5 |      |C#               |4.58 |1.18
6 |6 |      |Visual Basic     |4.12 |0.83
7 |7 |      |JavaScript       |2.54 |0.41
8 |9 |change|PHP              |2.49 |0.62
9 |19|change|R                |2.37 |1.33
10|8 |change|SQL              |1.76 |-0.19
11|14|change|Go               |1.46 |0.24
12|16|change|Swift            |1.38 |0.28
13|20|change|Perl             |1.3  |0.26
14|12|change|Assembly language|1.3  |-0.08
15|15|      |Ruby             |1.24 |0.03
16|18|change|MATLAB           |1.1  |0.04
17|11|change|Groovy           |0.99 |-0.52
18|33|change|Rust             |0.92 |0.55
19|10|change|Objective-C      |0.85 |-0.99
20|24|change|Dart             |0.77 |0.13

Přidání hlavičky do výsledné tabulky:

$ q -H -t -P -b -O "select * from tiobe2.tsv"
 
Sep 2020|Sep 2019|Change|Language         |Ratings|Changep
1       |2       |change|C                |15.95  |0.74
2       |1       |change|Java             |13.48  |-3.18
3       |3       |      |Python           |10.47  |0.59
4       |4       |      |C++              |7.11   |1.48
5       |5       |      |C#               |4.58   |1.18
6       |6       |      |Visual Basic     |4.12   |0.83
7       |7       |      |JavaScript       |2.54   |0.41
8       |9       |change|PHP              |2.49   |0.62
9       |19      |change|R                |2.37   |1.33
10      |8       |change|SQL              |1.76   |-0.19
11      |14      |change|Go               |1.46   |0.24
12      |16      |change|Swift            |1.38   |0.28
13      |20      |change|Perl             |1.3    |0.26
14      |12      |change|Assembly language|1.3    |-0.08
15      |15      |      |Ruby             |1.24   |0.03
16      |18      |change|MATLAB           |1.1    |0.04
17      |11      |change|Groovy           |0.99   |-0.52
18      |33      |change|Rust             |0.92   |0.55
19      |10      |change|Objective-C      |0.85   |-0.99
20      |24      |change|Dart             |0.77   |0.13

Výstup v odlišném formátu:

$ q -H -t -b -O "select * from tiobe2.tsv"
 
Sep 2020        Sep 2019        Change  Language                Ratings Changep
1               2               change  C                       15.95   0.74
2               1               change  Java                    13.48   -3.18
3               3                       Python                  10.47   0.59
4               4                       C++                     7.11    1.48
5               5                       C#                      4.58    1.18
6               6                       Visual Basic            4.12    0.83
7               7                       JavaScript              2.54    0.41
8               9               change  PHP                     2.49    0.62
9               19              change  R                       2.37    1.33
10              8               change  SQL                     1.76    -0.19
11              14              change  Go                      1.46    0.24
12              16              change  Swift                   1.38    0.28
13              20              change  Perl                    1.3     0.26
14              12              change  Assembly language       1.3     -0.08
15              15                      Ruby                    1.24    0.03
16              18              change  MATLAB                  1.1     0.04
17              11              change  Groovy                  0.99    -0.52
18              33              change  Rust                    0.92    0.55
19              10              change  Objective-C             0.85    -0.99
20              24              change  Dart                    0.77    0.13

15. Práce se vstupními soubory CSV

Nástroj q dokáže do určité míry pracovat i se vstupními soubory uloženými ve formátu CSV. Jedno z omezení (podle mě dosti nelogické) spočívá v tom, že nejsou správně rozpoznávány názvy sloupců umístěné do uvozovek. Na druhou stranu je však možné relativně bez problémů pracovat i s takovými CSV soubory, jejichž buňky obsahují znak pro konec řádku. To je ostatně i případ souboru, který budeme používat v demonstračních příkladech a který je dostupný na adrese https://github.com/tisnik/r-examples/blob/master/data/tests.csv:

id,module,name,file,doc,markers,status,message,duration
test_average.py::test_average_basic_1[values0-1],test_average,test_average_basic_1[values0-1],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0002028942108154297
test_average.py::test_average_basic_1[values1-1.5],test_average,test_average_basic_1[values1-1.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.00019598007202148438
test_average.py::test_average_basic_1[values2-0.5],test_average,test_average_basic_1[values2-0.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0002040863037109375
test_average.py::test_average_basic_1[values3-2.0],test_average,test_average_basic_1[values3-2.0],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001773834228515625
test_average.py::test_average_basic_1[values4-0.5],test_average,test_average_basic_1[values4-0.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",failed,"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5",0.0008950233459472656
test_average.py::test_average_basic_2[1.1],test_average,test_average_basic_2[1.1],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.00015044212341308594
test_average.py::test_average_basic_2[1.2],test_average,test_average_basic_2[1.2],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001647472381591797
test_average.py::test_average_basic_2[0.1],test_average,test_average_basic_2[0.1],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.00016117095947265625
test_average.py::test_average_basic_2[1.2.3],test_average,test_average_basic_2[1.2.3],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001361370086669922
test_average.py::test_average_basic_2[0.10],test_average,test_average_basic_2[0.10],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",failed,"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5",0.00025582313537597656
test_average.py::test_average_basic_3[values0-1],test_average,test_average_basic_3[values0-1],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001685619354248047
test_average.py::test_average_basic_3[values1-1.5],test_average,test_average_basic_3[values1-1.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.00015592575073242188
test_average.py::test_average_basic_3[values2-0.5],test_average,test_average_basic_3[values2-0.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001583099365234375
test_average.py::test_average_basic_3[values3-2.0],test_average,test_average_basic_3[values3-2.0],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",passed,,0.0001575946807861328
test_average.py::test_average_basic_3[values4-0.5],test_average,test_average_basic_3[values4-0.5],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",failed,"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5",0.00029540061950683594
test_average.py::test_average_basic_3[values5-0],test_average,test_average_basic_3[values5-0],test_average.py,Otestování výpočtu průměru.,"parametrize,smoketest",failed,ZeroDivisionError: float division by zero,0.00017714500427246094
test_average.py::test_average_empty_list_1,test_average,test_average_empty_list_1,test_average.py,Otestování výpočtu průměru pro prázdný vstup.,thorough,passed,,0.0004165172576904297
test_average.py::test_average_empty_list_2,test_average,test_average_empty_list_2,test_average.py,Otestování výpočtu průměru pro prázdný vstup.,thorough,passed,,0.0001773834228515625
test_average.py::test_average_five_values,test_average,test_average_five_values,test_average.py,Otestování výpočtu průměru.,,passed,,0.00015211105346679688
test_code_style.py::test_code_style,test_code_style,test_code_style,test_code_style.py,,,failed,"AssertionError: Detected 6 code style problems
assert 6 == 0
 +  where 6 = <pycodestyle.StandardReport object at 0x7f7a8e1a10f0>.total_errors",0.029315471649169922
Poznámka: povšimněte si například posledního řádku tabulky, v němž je chybové hlášení uložené na více řádcích. Ostatně toto je jeden z důvodů, proč klasické unixové nástroje nejsou příliš vhodné pro zpracování CSV.

16. Příklad dotazů nad CSV soubory

Podívejme se nyní na několik příkladů použití CSV jakožto vstupního formátu.

Explicitní specifikace separátoru, tedy znaku použitého pro oddělení prvků na jednom řádku:

$ q -H -d, "select count(*) from tests.csv"
20

Čtení hodnot z jednoho sloupce:

$ q -H -d, "select file from tests.csv"
 
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_average.py
test_code_style.py

Výpis testů, které zhavarovaly:

$ q -H -d, "select id, file from tests.csv where status='failed'"
test_average.py::test_average_basic_1[values4-0.5],test_average.py
test_average.py::test_average_basic_2[0.10],test_average.py
test_average.py::test_average_basic_3[values4-0.5],test_average.py
test_average.py::test_average_basic_3[values5-0],test_average.py
test_code_style.py::test_code_style,test_code_style.py

Víceřádkové prvky:

$ q -H -d, "select message from tests.csv where status='failed'"
 
"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5"
"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5"
"AssertionError: Očekávaná hodnota 0.5, vráceno 5.0
assert 5.0 == 0.5"
ZeroDivisionError: float division by zero
"AssertionError: Detected 6 code style problems
assert 6 == 0
 +  where 6 = <pycodestyle.StandardReport object at 0x7f7a8e1a10f0>.total_errors"

17. Klauzule distinct a group by

Velmi často se v SQL dotazech setkáme s klauzulemi distinct a group by. I tyto klauzule nástroj q pochopitelně podporuje.

Použití klauzule distinct – výpis souborů, v nichž jsou jednotkové testy uloženy:

$ q -H -d, "select distinct file from tests.csv"
 
test_average.py
test_code_style.py

Statistika – kolik testů zhavarovalo a kolik naopak prošlo bez chyby:

$ q -H -d, "select status, count(*) from tests.csv group by status"
failed,5
passed,15

18. Spojení tabulek v dotazu klauzulí join

V nástroji q je podporováno spojení tabulek klauzulí join. Každá tabulka musí být reprezentována samostatným souborem a všechny tabulky musí být uloženy ve stejném formátu (CSV, TSV, separátor atd.), protože není možné specifikovat formát pro každý vstupní soubor zvlášť. Vytvořme si tedy pomocnou tabulku, která bude obsahovat u každého jazyka, zda se jedná o překladač, assembler nebo interpret. Tabulka může vypadat následovně a naleznete ji na adrese

Language            Type
C                   compiler
Java                compiler
Python              interpreter
C++                 compiler
C#                  compiler
Visual Basic        interpreter
JavaScript          interpreter
PHP                 interpreter
R                   interpreter
SQL                 interpreter
Go                  compiler
Swift               compiler
Perl                interpreter
Assembly language   assembler
Ruby                interpreter
MATLAB              interpreter
Groovy              compiler
Rust                compiler
Objective-C         compiler
Dart                transpiler

19. Použití klauzule join

Spojení obou tabulek provedeme přes klasický příkaz tabulka1 join tabulka2 on vazební-podmínka. U tabulek však musíme uvést jejich jmenné aliasy, protože v názvu sloupců nelze použít přímo jméno souboru i s koncovkou. Použijme tedy aliasy „l“ a „t“:

Root online školení

$ q -t -H -b "select t.Language, Type, Ratings from tiobe2.tsv t join languages.tsv l on t.language=l.language"
 
C                       compiler        15.95
Java                    compiler        13.48
Python                  interpreter     10.47
C++                     compiler        7.11
C#                      compiler        4.58
Visual Basic            interpreter     4.12
JavaScript              interpreter     2.54
PHP                     interpreter     2.49
R                       interpreter     2.37
SQL                     interpreter     1.76
Go                      compiler        1.46
Swift                   compiler        1.38
Perl                    interpreter     1.3
Assembly language       assembler       1.3
Ruby                    interpreter     1.24
MATLAB                  interpreter     1.1
Groovy                  compiler        0.99
Rust                    compiler        0.92
Objective-C             compiler        0.85
Dart                    transpiler      0.77

Podobný výsledek, ovšem s odlišným výstupním formátem a s určením hlaviček sloupců:

$ q -t -H -b -O -P "select t.Language, Type, Ratings from tiobe2.tsv t join languages.tsv l on t.language=l.language"
 
Language         |Type       |Ratings
C                |compiler   |15.95
Java             |compiler   |13.48
Python           |interpreter|10.47
C++              |compiler   |7.11
C#               |compiler   |4.58
Visual Basic     |interpreter|4.12
JavaScript       |interpreter|2.54
PHP              |interpreter|2.49
R                |interpreter|2.37
SQL              |interpreter|1.76
Go               |compiler   |1.46
Swift            |compiler   |1.38
Perl             |interpreter|1.3
Assembly language|assembler  |1.3
Ruby             |interpreter|1.24
MATLAB           |interpreter|1.1
Groovy           |compiler   |0.99
Rust             |compiler   |0.92
Objective-C      |compiler   |0.85
Dart             |transpiler |0.77

Přidání podmínky:

$ q -t -H -b -O -P "select t.Language, Type, Ratings from tiobe2.tsv t join languages.tsv l on t.language=l.language where l.type = 'compiler'"
 
Language   |Type    |Ratings
C          |compiler|15.95
Java       |compiler|13.48
C++        |compiler|7.11
C#         |compiler|4.58
Go         |compiler|1.46
Swift      |compiler|1.38
Groovy     |compiler|0.99
Rust       |compiler|0.92
Objective-C|compiler|0.85

20. Odkazy na Internetu

  1. Comma-Separated Values
    https://en.wikipedia.org/wiki/Comma-separated_values
  2. Tab-Separated Values
    https://en.wikipedia.org/wiki/Tab-separated_values
  3. Delimiter-separated values
    https://en.wikipedia.org/wi­ki/Delimiter-separated_values
  4. q – Run SQL directly on CSV or TSV files
    https://harelba.github.io/q/
  5. q – examples
    https://harelba.github.io/q/#examples
  6. Repositář projektu q (GitHub)
    https://github.com/harelba/q
  7. How to run SQL queries directly on CSV or TSV file
    https://computingforgeeks.com/run-sql-queries-directly-on-csv-files/
  8. Tab separated values
    https://datatables.net/ex­tensions/buttons/examples/flash/tsv­.html
  9. csvkit 1.0.5 (dokumentace)
    https://csvkit.readthedoc­s.io/en/latest/#
  10. Repositář projektu csvkit (GitHub)
    https://github.com/wireservice/csvkit
  11. Příklad CSV schématu
    https://github.com/wireser­vice/ffs/blob/master/us/ir­s/irs_exempt_org_schema.csv
  12. Repositář projektu jq (GitHub)
    https://github.com/stedolan/jq
  13. GitHub stránky projektu jq
    https://stedolan.github.io/jq/
  14. 5 modern alternatives to essential Linux command-line tools
    https://opensource.com/ar­ticle/20/6/modern-linux-command-line-tools
  15. Návod k nástroji jq
    https://stedolan.github.i­o/jq/tutorial/
  16. jq Manual (development version)
    https://stedolan.github.io/jq/manual/
  17. Introducing JSON
    https://www.json.org/json-en.html
  18. jq.py: a lightweight and flexible JSON processor
    https://github.com/mwilliamson/jq.py
  19. Discover how to use jq, a JSON manipulation command line, with GeoJSON
    https://webgeodatavore.com/jq-json-manipulation-command-line-with-geojson.html
  20. Reshaping JSON with jq
    https://programminghistori­an.org/en/lessons/json-and-jq
  21. Python bindings for jq
    https://pypi.org/project/jq/
  22. edn
    https://github.com/edn-format/edn
  23. Why use JSON over XML?
    https://www.sitepoint.com/json-vs-xml/
  24. XML and XPath
    https://www.w3schools.com/XML/xml_xpat­h.asp
  25. XPath (Wikipedia)
    https://en.wikipedia.org/wiki/XPath
  26. RFC7159
    https://www.ietf.org/rfc/rfc7159.txt
  27. The Art of Unix Programming – DSV Style
    https://www.linuxtopia.or­g/online_books/programmin­g_books/art_of_unix_program­ming/ch05s02.html

Autor článku

Pavel Tišnovský vystudoval VUT FIT a v současné době pracuje ve společnosti Red Hat, kde vyvíjí nástroje pro OpenShift.io.