Več

Tabele za preoblikovanje in združevanje podatkov FME v DB brez ključa od dostopa do SQL


Uspelo mi je narediti pretvorbo (v FME2013) iz zbirke podatkov Access v bazo podatkov SQL Express (v namiznem delovnem pultu), pri čemer sem dodal nekaj transformatorjev za zbiranje podatkov iz treh tabel, od katerih imata dva primarne ključe, in izvajal SQL izvajalec poizvedb lahko zapolnim novo tabelo; poizvedovanje po potrebnih informacijah pri vseh treh, (ali notranje povezovanje obeh s ključem), vse to se izvaja po eno DB, ker se primarni ključi ponavljajo.

Zdaj bi rad zgrabil več zbirk podatkov Access DB naenkrat in naredil isto stvar, le da bi jih združil v skupine na podlagi baz podatkov, ki izvirajo iz dodajanja novega polja primarnega ključa, ki vsebuje ime datoteke Access DB v tem polju.

Ko uporabljam atribut polnega imena datoteke iz FilemnamePartExtractor, združuje vsa izbrana imena datotek in doda posebne znake (“, **), ki sem jih odstranil s funkcijo StringReplacer. Zaradi mojih omejenih izkušenj pri delu s FME se mi zdi, da ne morem uspešno napredovati.

Zanima me tudi, ali je to pravilen pristop ali obstaja bolj enostavna metoda?

V prvem in tretjem zapisovalniku uporabljam FilemnamePartExtractor, StringReplacer, StringConcatenator in BulkAttributeRenamer ter spuščam vrednosti v novo polje SourceData….


Po nekaj korakih naprej in nazaj s FME in boljšim poznavanjem njegove delovne plošče sem prišel do naslednje rešitve.

To težavo sem rešil tako, da sem uporabil samo vgrajeno poizvedbo in paketno razmestitev ... v meniju Datoteka; izvajanje paketnega uvajanja inicializira čarovnika, pri katerem izvede poizvedbo (-e) in zapisovalnike, da dokonča vsako vnosno bazo podatkov enega za drugim.

Ustvari naslednji želeni izhod. Rezultat: Kjer je polje SEGMENTID zdaj dodeljeno in uporabljeno kot identifikator za vsako klasifikacijo polja DISPLAY_NAME.


Vstavi v tabelo brez upoštevanja podvojenih vrednosti

Imam papiranje v možganih, da bi to ugotovil. Imam dve tabeli:

part_temp je moja začasna tabela, ki vsebuje podatke iz datoteke CSV, zato je samo en stolpec nastavljen na NIČ. Podatke iz part_temp moram vstaviti v dele.

Pravilno sem očistil podatke v tabeli, tako da ni ničelnih vrednosti, ki bi jih poskušali vstaviti v vrstice, ki zahtevajo vrednost. Moja težava pa je v moji omejitvi UNIQUE za stolpce part_number v tabeli delov. V tabeli part_temp so podvojene vrednosti, zato potrebujem način, da jih med vstavljanjem preskočim. To sem poskusil doslej, vendar ne deluje:

To so tabele, vključene v spoje, ki niso navedeni zgoraj

Kaj je narobe z mojo poizvedbo INSERT?

Posebna napaka, ki jo dobim, je:

Sporočilo 2627, raven 14, stanje 1, vrstica 1

Kršitev omejitve UNIQUE KEY. Podvojenega ključa ni mogoče vstaviti v objekt 'dbo.parts'. Podvojena vrednost ključa je (31335A11)

part_num 31335A11 se v datoteki csv pojavi večkrat. Kot taka se večkrat pojavi v tabeli part_temp. Preprosto bi bilo, če bi bil samo ta vnos, vendar imam več kot 1000 ponovljenih vnosov, zato bi trajalo večno, da odstranim vse podvojene. Po delih ne obstaja nič, saj je popolnoma nova prazna miza, v katero poskušam vnesti vrednosti.


4 odgovori 4

Ne vidim smisla uporabe RSA za razprševanje gesel. Uporaba SHA in RSA ne bo upočasnila napada bruteforce. Ogromni napadi GPU/ASIC bodo še vedno delovali, če predpostavimo, da je javni ključ $ (e, n) $ znan. Zato za upočasnitev napadov potrebujemo trde funkcije pomnilnika. Upoštevanje standarda je še vedno boljše kot pri uporabi Argon2id (Argon2 je bil leta 2015 zmagovalec natečaja za razprševanje gesel). Edinstvena sol pomaga tudi pri odpravljanju mavričnih miz. Mavrične mize so mrtve za sistem gesel, ki uporablja edinstvene soli !.

Manjša točka je, da ni treba shranjevati zasebnega ključa RSA $ (d, n) $, ker ni mogoče razveljaviti SHAx. Torej je neuporabno.

Nazaj na Rainbow

v primeru zaščite pred mavričnimi mizami se morate prepričati, da vsako geslo potrebuje ločitev domene. To dosežemo z edinstveno soljo za vse. Če želite uporabiti RSA, morate uporabiti oblazinjenje OAEP ali PKCS#1 v1.5. oblazinjenje. Oba sta verjetnostna shema šifriranja, če imate dober vir naključnih številk, kot je /dev /urandom, boste, če znova in znova šifrirate isto sporočilo, dobili različne rezultate, seveda do velike omejitve (velikost $ r $ v OAEP). O soli lahko razmišljamo kot o tem naključju.

Stranska opomba: poper, ki je edinstvena sol za vsak aplikacijski strežnik, se uporablja za ločevanje domen aplikacij v primeru zadetka iste soli za istega uporabnika. Če napadalec prenese tabelo uporabnikov samo z injekcijo SQL, potem ne more uporabiti niti brutalne sile brez poper strežnika.

Opomba 2: Po seznamu Hashcat samo OpenSSH uporablja RSA v kombiniranem načinu RSA/DSA/EC/OpenSSH

Ta del temelji na komentarjih @fgrieu v primeru, da jih je @marcus upošteval

Tukaj deterministično oblazinjenje pomeni dodajanje sporočila za šifriranje RSA, vendar deterministično, kot je RSASSA-PKCS1-v1_5.

Očitno je, da če je sol edinstvena za vsakega uporabnika, potem je že zaščitena pred mavričnimi mizami. Krekerji gesel, če imajo dostop do razpršilnikov gesel, ga ne morejo preizkusiti brez vednosti zasebnega ključa.

Največji problem je zaščita zasebnega ključa RSA. Običajen pristop je uporaba HSM za obravnavo tistih šifriranj, kjer je shranjen tudi ključ RSA, vendar je pri težkih sistemih lahko ozko grlo za hitrost. To ni prava primerjava in običajen nasvet za algoritme za razprševanje gesel je prilagoditev ponovitve tako, da traja približno 1 sekundo na uporabnika. To je za prijaznost do uporabnika. torej splošni uporabnik morda ne bo čakal preveč na postopek prijave.

Poznavanje javnega ključa $ (n, e) $ napadalcem ne bo pomagalo, saj po mnenju javnosti ne morejo prekiniti RSA & gt 829-bit. Oglejte si trenutne zapise o tem, kako velik ključ RSA danes velja za varen?

To operacijo RSA lahko obravnavamo tudi kot poper strežnika aplikacij. Namesto RSA lahko za isto uporabo uporabite HMAC-SHA256, ki ima manjšo velikost ključa.

V kratkem, če je ključ mogoče zaščititi, ima več zaščite pred običajnim pristopom.

To, kar opisujete, se imenuje poper.

Kar počnete, je samo uporaba RSA kot kriptografske hash funkcije. To verjetno zmanjša zmogljivost in naredi vaš sistem bolj zapleten.

Na splošno ljudje namestijo naključno število neposredno v program kot dobesedno. Varno je, dokler je vaša izvorna koda in binarna koda varna. Lahko ga uporabite kot ključ RSA, vendar je učinkovitejši način, da poper dodate na enak način kot sol. (namig je v imenu.)

V bistvu vzamete geslo, dodate sol, dodate poper in jih združite.

Mavrične mize so v bistvu optimiziran slovarski napad, ki temelji na dveh predpostavkah:

  • Da bosta dve različni aplikaciji enak vhod združili v isti izhod, npr. geslo & quotPassword123 & quot bo vedno razpršeno v & quot42f749ade7f9e195bf475f37a44cafcb & quot. To omogoča napadalcu, da ponovno uporabi mavrično bazo podatkov za napad na več ciljev.
  • To sta dva različna vnosa v eni aplikaciji bo enak vhod razpršil v isti izhod, npr. dva različna uporabnika z geslom & quotPassword123 & quot bosta imela shranjen isti hash. To omogoča napadalcu, da izračuna hashe na podlagi slovarja in jih preizkusi proti vsem vnosom hkrati.

Dodajanje dodatnih korakov v postopek zgoščevanja-dodajanje globalnega & quotpepper & quot, dvojnega zgoščevanja, šifriranje-potem-zgoščevanje-bo na splošno prekinilo prvi predpostavka: vaš hash za & quotPassword123 & quot; zdaj ni več videti kot moj hash za & quotPassword123 & quot.

Vendar, da bi prekinil drugič predpostavka, da morate narediti nekaj drugačnega za vsak vnos v vaši prijavi, in to je tisto, kar dodaja sol. Edini način, kako bi korak šifriranja opravljal isto funkcijo, je, če bi slučajno vseboval svojo sol, pri čemer je šifriranje v bistvu nepomembno.

Na splošno je slaba ideja, da uvedete lastne kripto algoritme ali jih nenamerno združite, ne da bi resnično poznali osnovno teorijo, zato se raje držite dobro razumljene hash-funkcije (geslo + na -uporabniška sol + po aplikaciji-poper).

Tako razpršitev (na primer SHA-1) kot šifra (na primer RSA) sta zasnovana tako, da ju ni mogoče obrniti. To pomeni, da glede na njihove rezultate (povzetek ali šifrirani tekst) ne bi bilo mogoče ugotoviti, kakšen je bil vložek.

Vrednost soli ni v tem, da je težje ugotoviti, kakšno je bilo geslo iz zgoščenega gesla. Gesla je pogosto enostavno uganiti. Sol olajša ugibanje napadalca, ker je sol za vsakega uporabnika drugačna. Tako mora napadalec ugibati posamezne uporabnike (& quotuser 3198721 ima geslo 'qwerty' & quot) in ne ugibati celotne baze podatkov naenkrat (& quotsome uporabnik v tej bazi ima geslo 'qwerty' & quot).

To znatno poveča število ugibanj, ki jih je treba narediti, in s tem čas, potreben za uspešno ogrožanje uporabnika, še posebej, če je funkcija razpršitve izbrana kot draga za izračun, saj so vse dobre funkcije razpršitve gesel. Dobro zasnovan sistem nikoli ne bi uporabljal SHA, ampak nekaj takega, kot so bcrypt, scrypt ali PBKDF2.

Dodajanje šifriranja RSA v mešanico samo po sebi ne doseže tega, kar počne sol. Morda pri šifriranju RSA povzetek & quotqwerty & quot ni & quot63edc12362821bd115f7 & quot, ampak & quotee69076c5a27c1c476 & quot. Vendar je še vedno enako za vsakega uporabnika, zato je eno ugibanje mogoče preveriti pri vseh uporabnikih v bazi podatkov.

Nekaj ​​koristi je v tem, da mora napadalec ogroziti tudi šifrirni ključ, kar je nekaj vredno. To pa običajno dosežemo tako, da celotno bazo uporabnikov shranimo v šifrirano shrambo, namesto da šifriramo vsako geslo posebej, preden ga zgostimo. En razlog: omogoča vrtenje šifrirnega ključa.

Vendar nekatere sheme šifriranja uporabljajo inicializacijski vektor (IV), ki je podoben soli, saj je naključna vrednost. RSA ne uporablja vedno IV, vendar bi lahko, v tem primeru bi vaša predlagana shema v bistvu solila gesla z drugim imenom.


To lahko storite na več načinov, odvisno od tega, kaj počnete res želim. Brez skupnih stolpcev se morate odločiti, ali želite uvesti skupni stolpec ali dobiti izdelek.

Recimo, da imate dve mizi:

Pozabite na dejanske stolpce, saj bi najverjetneje imeti odnos stranka/naročilo/del v tem primeru sem uporabil te stolpce za ponazoritev načinov za to.

Kartezijski izdelek se bo ujemal z vsako vrstico v prvi tabeli z vsako vrstico v drugi:

Verjetno tega ne želite, saj bi 1000 delov in 100 strank povzročilo 100.000 vrstic z veliko podvojenimi informacijami.

Druga možnost je, da uporabite združitev za samo izpis podatkov, čeprav ne vzporedno (prepričati se morate, da so vrste stolpcev združljive med obema izbirama, tako da stolpce tabele združite ali jih prisilite pri izbiri ):

V nekaterih bazah podatkov lahko uporabite stolpec vrstice/vrstice ali psevdo-stolpec za ujemanje zapisov drug ob drugem, na primer:

Koda bi bila nekaj takega:

Še vedno je kot kartezični izdelek, vendar klavzula where omejuje, kako so vrstice združene, da se oblikujejo rezultati (torej sploh ne kartezični izdelek).

Tega SQL-a za to nisem preizkusil, ker je to ena od omejitev mojega izbranega DBMS-ja in upravičeno ne verjamem, da bi bil kdaj potreben v ustrezno premišljeni shemi. Ker SQL ne jamči vrstnega reda, v katerem proizvaja podatke, se lahko ujemanje spremeni vsakič, ko izvedete poizvedbo, razen če imate specifično razmerje ali odredba po klavzuli.

Mislim, da bi bilo idealno, da bi v obe tabeli dodali stolpec, v katerem bi določili razmerje. Če ni resničnega odnosa, potem verjetno nimate ničesar pri tem, da bi jih poskušali postaviti vzporedno s SQL.

Če želite, da so v poročilu ali na spletni strani prikazani drug ob drugem (dva primera), je to pravo orodje, kar ustvari vaše poročilo ali spletno stran, skupaj z dvema neodvisni SQL poizvedbe za pridobitev dveh nepovezanih tabel. Na primer mreža z dvema stolpcema v BIRT (ali Crystal ali Jasper), vsaka z ločeno podatkovno tabelo, ali tabela dveh stolpcev HTML (ali CSS), vsaka z ločeno podatkovno tabelo.


Korak 2) Preoblikovanje

Podatki, pridobljeni iz izvornega strežnika, so surovi in ​​v prvotni obliki niso uporabni. Zato ga je treba očistiti, preslikati in preoblikovati. Pravzaprav je to ključni korak, kjer postopek ETL doda vrednost in spremeni podatke, tako da je mogoče ustvariti podrobna poročila BI.

To je eden od pomembnih konceptov ETL, kjer za izvlečene podatke uporabite niz funkcij. Podatki, ki ne zahtevajo preoblikovanja, se imenujejo neposredna poteza ali prenašati podatke.

V koraku preoblikovanja lahko izvajate prilagojene operacije s podatki. Na primer, če uporabnik želi vsoto prihodkov od prodaje, ki jih ni v bazi podatkov. Ali če sta ime in priimek v tabeli v različnih stolpcih. Pred nalaganjem jih je mogoče združiti. Težave pri integraciji podatkov

  1. Različen črkovanje iste osebe, kot so Jon, John itd.
  2. Ime podjetja je na več načinov, na primer Google, Google Inc.
  3. Uporaba različnih imen, kot so Cleaveland, Cleveland.
  4. Lahko se zgodi, da različne aplikacije za isto stranko ustvarijo različne številke računov.
  5. V nekaterih podatkih zahtevane datoteke ostanejo prazne
  6. Neveljaven izdelek, zbran na POS, saj lahko ročni vnos povzroči napake.
  • Filtriranje - izberite samo nekatere stolpce za nalaganje
  • Uporaba pravil in iskalnih tabel za standardizacijo podatkov
  • Pretvorba nabora znakov in ravnanje s kodiranjem
  • Pretvorba merskih enot, kot so pretvorba datuma in časa, pretvorba valut, številska pretvorba itd.
  • Preverjanje veljavnosti praga podatkov. Na primer, starost ne sme biti več kot dve števki.
  • Validacija pretoka podatkov od območja uprizoritve do vmesnih tabel.
  • Obvezna polja ne smejo ostati prazna.
  • Čiščenje (na primer preslikava NULL na 0 ali Spol Moški na "M" in Ženska na "F" itd.)
  • Razdelite stolpec na večkratnike in združite več stolpcev v en sam stolpec.
  • Prenos vrstic in stolpcev,
  • Za iskanje podatkov združite podatke
  • Uporaba kakršnega koli zapletenega preverjanja podatkov (npr. Če sta prva dva stolpca v vrstici prazna, samodejno zavrne obdelavo vrstice)

Delo s Kudujem integrirano s Hive Metastore

Od Kudu 1.10 in Impala 3.3 Impala podpira storitve Kudu, integrirane s Hive Metastore (HMS). Za več podrobnosti o integraciji Kudu's Hive Metastore si oglejte dokumentacijo o integraciji HMS.

  • Ko je Kudu integriran s Hive Metastore, mora biti Impala nastavljena tako, da uporablja isti HMS kot Kudu.
  • Ker med tabelami Kudu in zunanjimi tabelami morda ni preslikave ena na ena, se samodejno sinhronizirajo samo notranje tabele.
  • Ko ustvarite tabelo v Kuduju, bo Kudu za to tabelo ustvaril vnos HMS z vrsto notranje tabele.
  • Ko je storitev Kudu integrirana s HMS, bodo v HMS samodejno ustvarjeni notranji vnosi tabel, ko bodo v Kuduju ustvarjene tabele brez Impale. Za dostop do teh tabel prek Impale zaženite stavek INVALIDATE METADATA, da Impala pobere najnovejše metapodatke.

Naš blog

Ena od naših strank je imela v Linuxu sklad aplikacij, ki temelji na Javi, ki je bil povezan s precej staro različico strežnika SQL Server v sistemu Windows. Celoten sistem smo želeli preseliti v skladnejši enoten sklad, s katerim so razvijalci učinkoviti, in to je trenutno, zato redno prejema posodobitve.

Odločili smo se, da bomo zbirko podatkov preselili iz strežnika SQL Server v PostgreSQL v sistemu Linux, ker je bilo prenos podatkovne zbirke, čeprav ne povsem hitro ali preprosto, še vedno veliko enostavnejše, kot bi bilo prenos aplikacije v .NET/C#. Ponovno pisanje aplikacije bi trajalo veliko dlje, bi bilo za podjetje veliko bolj tvegano in bi stalo veliko več.

Preizkusil sem nekaj pristopov k selitvi in ​​se odločil, da nadaljujem s postopkom selitve sheme in nato s pristopom selitve podatkov, ki je omenjen na wikiju Postgres. Pojdimo skozi postopek selitve korak za korakom.

Migracija sheme

Za pretvorbo sheme je treba izvoziti shemo tabel in pogledov zbirke podatkov SQL Server. Naslednji koraki vam bodo pokazali, kako izvozite shemo.

Izvozi shemo zbirke podatkov SQL Server

V programu SQL Management Studio z desno miškino tipko kliknite zbirko podatkov in izberite Opravila → Ustvari skripte.

Izberite »Izberi določene objekte zbirke podatkov« in preverite samo tabele sheme aplikacije (počistite predmete sheme dbo in druge, če obstajajo).

Prepričajte se, da je »Vrste podatkov za skript« v naprednih možnostih nastavljeno na »Samo shema«.

Preglejte in shranite datoteko sheme tabel zbirk podatkov tables.sql. Uporabite WinSCP in avtorizacijo javnega ključa za prenos tables.sql na strežnik Linux.

Pretvorite shemo iz strežnika SQL v Postgres

sqlserver2pgsql je dobro orodje za selitev, napisano v Perlu za pretvorbo shem SQL Server v sheme Postgres. Klonirajte ga iz GitHub v strežnik baz podatkov in izvedite naslednje ukaze za pretvorbo sheme tabel:

Pretvorjena shema bo na voljo v tabelah-before.sql, stavki za ustvarjanje omejitev pa bodo v tabelah-po.sql za izvedbo po selitvi podatkov. Preglejte tabele-unsure.sql in naredite vse, kar je potrebno, če orodje ne pretvori tabel. Če želite spremeniti imena shem v Postgresu, jih lahko zdaj preimenujete v datoteki SQL, na primer:

Nastavitev zbirke podatkov Postgres:

Upajmo, da imate v strežniku pripravljeno zbirko podatkov Postgres. V nasprotnem primeru na strežnik namestite najnovejšo različico programa Postgres. Nato ustvarite uporabnika in bazo Postgres z dovoljenji, dodeljenimi uporabniku.

Ko sta zbirka podatkov in uporabniški račun pripravljena, naložite pretvorjeni skript-before.sql v bazo podatkov, da ustvarite tabele. Nato lahko nadaljujemo z migracijo podatkov.

Migracija podatkov

Zaradi selitve in obnove podatkov je postopek okoren, z velikim ročnim postopkom, neusklajenostjo podatkovnih tipov, oblikami datumov itd. Pametno je uporabiti obstoječe stabilne sisteme, namesto da bi vanj vlagali več časa.

Pentaho integracija podatkov

Pentaho ponuja različne stabilne izdelke, osredotočene na podatke. Pentaho Data Integration (PDI) je orodje ETL, ki zagotavlja odlično podporo za selitev podatkov med različnimi bazami podatkov brez ročnega posredovanja. Skupna izdaja PDI je dovolj dobra za opravljanje naše naloge tukaj. Vzpostaviti mora povezavo z izvorno in ciljno bazo podatkov. Nato bo preostalo delo opravil pri selitvi podatkov iz strežnika SQL v bazo Postgres z izvajanjem opravila PDI.

Prenesite Pentaho Data Integration Community Edition in izvlecite tarball v svojem lokalnem okolju. Glej:

Zaženite spoon.sh, da odprete aplikacijo GUI v vašem lokalnem okolju.

Ustvarite povezave z bazami podatkov SQL Server in Postgres v PDI.

2. Ustvarite izvorno povezavo z zbirko podatkov.

V levi stranski vrstici kliknite Pogled → Desni klik »Povezave z zbirko podatkov« → Izberite Novo → Navedite podrobnosti o povezavi s strežnikom SQL Server

3. Ustvarite povezavo z zbirko podatkov o destinaciji.

V levi stranski vrstici kliknite Pogled → Desni klik »Povezave z zbirko podatkov« → Izberite Novo → Navedite podrobnosti o povezavi Postgres

4. V meniju Čarovnik izberite Čarovnik za kopiranje tabel.

Orodja → Čarovnik → Kopiraj tabele

5. Izberite izvorno in ciljno zbirko podatkov.

6. Izberite seznam tabel za selitev.

7. Premaknite se naprej, da izberete pot za datoteke opravil in preoblikovanje.

8. Transformacije so bile ustvarjene za kopiranje podatkov iz izvorne v ciljno bazo podatkov.

9. Kopirajte tarball in opravilo Pentaho Data Integration s transformacijami na strežnik Postgres, da se izognete zamudam pri selitvi podatkov.

10. Preverite dostop Postgres strežnika do baze podatkov SQL Server (na podlagi zasnove arhitekture na vašem koncu).

Vzpostavite tuneliranje SSH za povezavo s strežnikom SQL prek strežnika aplikacij.

PDI Job Execution

Izvedite PDI opravilo s kuhinjskim pripomočkom Pentaho na strežniku baz podatkov. Dodajte konfiguracijo kotlička, da se izognete temu, da bi Pentaho prazne vrednosti upošteval kot vrednosti NULL, kar vpliva na omejitve NOT NULL.

Potrdite naslov IP za povezave strežnikov baz podatkov in izvedite opravilo s pomočjo kuhinjskega orodja ukazne vrstice za izvajanje opravil PDI.

Postopek selitve podatkov je trajal približno 30 minut za kopiranje 10 GB podatkov iz strežnika SQL Server v bazo podatkov Postgres po omrežju.

Med kopiranjem velike tabele smo naleteli na težavo s prostorom in jo obravnavali z zmanjšanjem števila vrstic v ustrezni datoteki za preoblikovanje tabele. Tukaj je razprava o problemu.

Ko je selitev podatkov končana, izvedite skript za selitev sheme-tabele-after.sql za uporabo omejitev.

Preseli poglede

Za pretvorbo sheme views.sql sledite istim korakom. Z desno miškino tipko kliknite zbirko podatkov in kliknite Opravila → Ustvari skript → Izvozi samo poglede.

Prenesite funkcije

Migracija funkcij zahteva znanje in skladnost tako SQL Serverja kot Postgresa. Po ponovnem pisanju za Postgres je treba funkcije ustrezno potrditi in preizkusiti. Vse prepisane funkcije postavite v postgres-function.sql in naložite v bazo podatkov kot del procesa selitve.

Najpogostejše napake

NAPAKA: sintaktična napaka pri ali blizu & quot [& quot
Odstranite oglate oklepaje SQL Server, ki navajajo imena imen stolpcev in tabel.
SELECT [user_id] → SELECT user_id

NAPAKA: operator ne obstaja: spreminjanje znakov = celo število
Tips litje za primerjavo
AND ime_ stolpca v (1, -1) → AND_name_stolona v (Ƈ ', '-1 ')

NAPAKA: sintaktična napaka pri ali blizu & quot, & quot
Pretvorba podatkovnega tipa
pretvoriti (varchar (10), ime_ stolpca) → ime_ stolpca :: besedilo

NAPAKA: stolpec & quotuser_id & quot odnosa & quotuser & quot ne obstaja
Brez dvojnih narekovajev pri imenih stolpcev z mešanimi črkami (razen če se uporabljajo povsod)
INSERT INTO user (& quotuser_id & quot) VALUES (1) → INSERT INTO user (user_id) VALUES (1)

NAPAKA: stolpec & quotmm & quot ne obstaja
DATEADD → INTERVAL
DATEADD (mm, -6, GETDATE ()) → CURRENT_DATE - INTERVAL ƌ mesecev '

NAPAKA: stolpec & quotvarchar & quot ne obstaja
Pretvorite datum v obliko ZDA
pretvori (varchar, tc_agreed_dt, 101) → v_char (CURRENT_TIMESTAMP, 'MM/DD/LLLL ')

Rezultat

Po selitvi smo izvedli obsežno testiranje aplikacije in optimizirali poizvedbe v različnih modulih. Izvedli smo tudi primerjalni test hitrosti in zmogljivosti med novim in starim sistemom. Selitev se je obrestovala z obetavnim rezultatom 40 -odstotnega povprečnega povečanja skupne uspešnosti aplikacije. To odraža novejšo strežniško strojno opremo in optimizacijo poizvedb, ne le prehod na Postgres.


Niz lahko sestavite s pogledi kataloga, na primer:

Seveda obstajajo potencialni problemi, na primer, če imajo te tabele odnose s tujimi ključi, jih morate najprej spustiti ali pa urediti izhod za spustitev tabel v določenem vrstnem redu.

Če želite le dobiti seznam tabel, uporabite:

Zagnala sem to poizvedbo in nato prilepila rezultate nazaj v okno poizvedbe, da izpustim vse tabele:

Če želite izbrisati vse tabele, vendar ohraniti tiste z imeni, ki so se začela z A, B, C ali D:

To vam omogoča, da izbrišete veliko večje število tabel.

Všeč mi je tale, ki sem ga napisal:

To lahko storite z izvedbo na naslednji način:

Zaženi nad poizvedbo in shrani rezultate v datoteko csv. Nato odprite ta CSV v beležnici. Nato naredite kombinacijo tipk Ctrl+H, da shemo zamenjate z DROP TABLE SCHEMA, ki vam bo dala vse poizvedbe za spustitev, kopirajte in prilepite ta veliki sql v orodje sql in ga izvedite


Primarni ključ SQLite in vrstna tabela

Ko ustvarite tabelo, ne da bi podali možnost WITHOUT ROWID, SQLite doda implicitni stolpec, imenovan rowid, ki shranjuje 64-bitno podpisano celo število. Vrstni stolpec je ključ, ki enolično identificira vrstice v tabeli. Tabele z vrstnimi stolpci se imenujejo vrstne tabele.

Če ima tabela primarni ključ, ki je sestavljen iz enega stolpca, in je ta stolpec definiran kot INTEGER, potem ta stolpec primarnega ključa postane vzdevek za stolpec vrstice.

Upoštevajte, da če stolpcu primarnega ključa dodelite drugo vrsto celega števila, na primer BIGINT in UNSIGNED INT, ta stolpec ne bo vzdevek stolpca vrstice.

Ker vrstica tabel organizira podatke kot drevo B, so poizvedovanje in razvrščanje podatkov vrstice tabele zelo hitri. Je hitrejši kot uporaba primarnega ključa, ki ni vzdevek vrstice.

Druga pomembna opomba je, da če deklarirate stolpec z vrsto INTEGER in klavzulo PRIMARY KEY DESC, ta stolpec ne bo postal vzdevek za stolpec rowid:


Povzemanje in združevanje podatkov v SQL

Lahko uporabiš IZRAZIT za odpravo podvojenih vrednosti v izračunih združenih funkcij glejte “ Odstranitev podvojenih vrstic z IZRAZIT” v poglavju 4. Splošna sintaksa združene funkcije je:

agg_func je MIN, MAX, SUM, AVG, oz COUNT. ekspr je ime stolpca, dobesednost ali izraz. VSE uporabi združeno funkcijo za vse vrednosti in IZRAZIT določa, da se upošteva vsaka edinstvena vrednost. VSE je privzeto in se v praksi le redko pojavlja.

Z SUM (), AVG (), in COUNT (ekspr), IZRAZIT odpravlja podvojene vrednosti, preden se izračuna vsota, povprečje ali štetje. IZRAZIT ni smiselno z MIN () in MAX () lahko ga uporabite, vendar rezultat ne bo spremenil. Ne morete ga uporabiti IZRAZIT z COUNT (*).

Za izračun vsote niza različnih vrednosti:

ekspr je ime stolpca, dobesedni ali številski izraz. Podatkovni tip rezultatov je vsaj tako natančen kot najbolj natančen tip podatkov, ki se uporablja ekspr.

Za izračun povprečja niza različnih vrednosti:

ekspr je ime stolpca, dobesedni ali številski izraz. Podatkovni tip rezultatov je vsaj tako natančen kot najbolj natančen tip podatkov, ki se uporablja ekspr.

Če želite šteti različne vrstice, ki niso ničelne:

ekspr je ime stolpca, dobesednost ali izraz. Rezultat je celo število večje ali enako nič.

Poizvedbe v Seznam 6.6 vrne število, vsoto in povprečje cen knjig. Ne-IZRAZIT in IZRAZIT Rezultati v Slika 6.6 se razlikujejo, ker se IZRAZIT rezultati odpravljajo podvojene cene 12,99 USD in 19,95 USD iz izračunov.

Seznam 6.6 Nekateri IZRAZIT združene poizvedbe. Za rezultate glejte sliko 6.6.

Slika 6.6 Rezultati uvrstitve 6.6.

IZRAZIT v IZBERI klavzulo inIZRAZIT v skupni funkciji ne vrni istega rezultata.

Tri poizvedbe v Seznam 6.7 štejte ID -je avtorjev v tabelititle_authors. Slika 6.7 prikazuje rezultate. Prva poizvedba šteje vse ID -je avtorjev v tabeli. Druga poizvedba vrne enak rezultat kot prva poizvedba, ker COUNT () je že opravil svoje delo in prej vrnil vrednost v eno vrstico IZRAZIT se uporablja. V tretji poizvedbi IZRAZIT se prej uporablja za ID -je avtorjev COUNT () začne šteti.

Seznam 6.7 IZRAZIT v IZBERI klavzulo in IZRAZIT v skupni funkciji se razlikujejo po pomenu. Za rezultate glejte sliko 6.7.

Slika 6.7 Rezultati uvrstitve 6.7.

Mešanje brezIZRAZIT in IZRAZIT agregati v istem IZBERI klavzula lahko povzroči zavajajoče rezultate.

Štiri poizvedbe v Seznam 6.8 (naslednja stran) prikazujejo štiri kombinacije neIZRAZIT in IZRAZIT vsote in štetja. Od štirih rezultatov v Slika 6.8 (naslednja stran), samo prvi rezultat (št IZRAZITs) in končni rezultat (vse IZRAZITs) so matematično skladni, kar lahko preverite AVG (cena) in AVG (DISTINCT cena). V drugi in tretji poizvedbi (mešaneIZRAZITs in IZRAZITs), veljavnega povprečja ne morete izračunati tako, da vsoto delite s štetjem.

Navedba 6.8 Mešanje brezIZRAZIT in IZRAZIT agregati v istem IZBERI klavzula lahko povzroči zavajajoče rezultate. Za rezultate glejte sliko 6.8.

Slika 6.8 Rezultati uvrstitve 6.8.

Microsoft Access ne podpira IZRAZIT agregatne funkcije. Ta izjava je na primer v Accessu nezakonita:

Lahko pa ga podvojite s to podpoizvedbo (glejte Nasvete v “Uporaba podvprašanj kot izrazov stolpcev ” v 8. poglavju):

Ta rešitev za Access vam ne bo omogočila mešanja ne-IZRAZIT in IZRAZIT agregati, tako kot v drugi in tretji poizvedbi v seznamu 6.8.

V Microsoft SQL Server, če uporabljate IZRAZIT, ekspr mora biti samo ime stolpca. Ne more vsebovati aritmetičnega izraza:

MySQL podpira COUNT (DISTINCTT ekspr) vendar ne SUM (DISTINCT ekspr) in AVG (ODLIČNO ekspr). Uvrstitve 6.6 in 6.8 se v MySQL ne bodo prikazovale.


Poglej si posnetek: Trikovi u Excelu - Dizajn pivot tabele (September 2021).