Mai Tsara SQL — MySQL, PostgreSQL, BigQuery, Snowflake Online

Ƙungiyoyin injiniyoyin bayanan da ke a Najeriya da faɗin Afirka ta Yamma suna ganin tsara SQL a matsayin mataki na tsaro kafin in tura lambar. Idan mai haɓaka ɗaya ya tsara hannu ta layi CTE na 200 kuma wani ya liƙa shi a matsayin layi ɗaya, bambancin PR yana fashe zuwa amo da ke ɓoye canjin ma'ana na ainihi. Tsara tambayoyi ta hanyar mai tsara kafin tura lambar na yanke zagayen nazari kuma yana sa canjin da mai nazari ke buƙatar karanta ya zauna a kan layin sa.

Yaya tsara SQL ke aiki

Tsara SQL sake rubuta da lexer ke jagoranta wanda yake wucewa a kan kowane token a cikin tambayar kuma yana sake fitar da sarari a kewayenta bisa ga ƙa'idodin yaren da aka zaɓa. Ma'anar tambaya ba ta canzawa, tsari kawai.

Zaɓi yaren SQL. Backticks na MySQL, jefa nau'i na PostgreSQL ::, hanyoyin tebur masu aya na BigQuery, da masu tanadar ɗakunan murabba'i na T-SQL — kowannensu yana buƙatar tokenizer da ke san su. Zaɓin yaren SQL yana zaɓar wace nahawu ta amfani.
Raba shigarwa zuwa tokens. Mai tsara yana raba tambayar zuwa jerin tokens: keywords (SELECT, JOIN), masu tanadar, ƙimomi, masu aiki, ƙofofin gefe, da sharhi. Ana wuce ta kirtanin gaske da masu tanadar da aka naɗa ba tare da taɓawa ba domin tsarin da ya keɓanci yaren SQL ya ci gaba da aiki.
Amfana da ƙa'idodin tsari. Yankuna matakin sama (SELECT, FROM, WHERE, GROUP BY, ORDER BY) suna farawa a kan layin su na musamman. Bayananai da aka raba da comma a cikin jerin zaɓe da jerin ginshiƙi kowannensu yana samun layi, da shiga ta hanyar rukunin shiga da aka zaɓa.
Amfana da halin keyword. Maɓallin halin keyword yana sake rubuta keywords na SQL da aka gane zuwa babba, ƙanƙane, ko kiyaye halin shigarwa a zahiri. Masu tanadar ba a taɓa su — sunaye na ginshiƙi da tebur koyaushe suna zo kamar yadda aka rubuta.
Fitar da kirtanin da aka tsara. An haɗa jerin token zuwa kirtani ɗaya tare da halin shiga (sarari 2, sarari 4, ko tab) da ƙa'idar karaya layi da aka tsara. Yanayin kai tsaye yana sake gudanar da duka tsarin akan jinkirta ms 200 yayin da kake rubutu.

Me ya sa tsara SQL

Karanta bambancin pull-request. CTE na layi 200 da aka sake rubuta a matsayin layi ɗaya yana mai da nazarin lambar wasan tsammani. Tsara da dacewa yana kiyaye bambancin mai kewayon canjin da ka yi ainihi — ginshiƙin sabon, JOIN ƙarin, nau'ikan WHERE daban — don mai nazarin ya iya ganin sa ba tare da kwance sarari ba.
Debugging mafi sauƙi. Lokacin da tambaya ta dawo da ƙidayar layi mara daidai, abu na farko da kake yi shi ne karanta ta layi ta layi. SQL da aka tsara yana sanya kowane JOIN a kan layin sa kuma yana layana nau'ikan WHERE don AND da ya ɓace ko OR mai yawo ya bayyana a ɗan kallo.
Salon ƙungiya mai dacewa. Yawancin ƙungiyoyi suna karɓar jagorar salon SQL (dbt Labs, GitLab, Mode Analytics duk suna buga nasu) kuma suna son kowane tambayar da aka tura ta bi ta. Mai tsara da aka gudanar kafin tura yana cire jayayya ta salon daga nazari kuma yana bari rikodi don tattauna ma'ana kawai.
Raba SQL a cikin takardun. Runbooks, retrospectives na tukunci, da takardun Notion duk suna amfana daga SQL da ke karanta sama-ƙasa. SQL da aka tsara yana liƙa a cikin ɓangaren lambar da a naɗe kuma yana buga daidai a fitar PDF, ba tare da rurkuwar layi mara kyau a tsakiyar keyword ba.

Amfani na gama-gari

Tsara SQL yana bayyana a duk injiniyanci na analytics, haɓakawa na baya, da ayyukan aiki koyaushe tambaya ta zama dole wani da bai rubuta ta ba ya karanta ta.

Injiniyanci na analytics: hook na kafin-tura a cikin aikin dbt wanda ke sake tsara kowane fayil ɗin model don bambancin PR ya kasance mai kewayon canjin ma'ana, ba kauyawan sarari ba.
Gudanar da bayanan bayanai: liƙa shigarwa ta log ɗin tambayar hankali ta layi ɗaya, tsara shi, ka bi ta tsari na haɗa yayin rubuta retrospective na tukunci.
Takardun: ɗauki tambayar daga Looker explore ko littafin aikin Tableau, tsara shi don runbook, ka dasa shi a matsayin misali mai iya kwafuwa don juyawar kan-kira.

Misali na aiki

Liƙa SELECT u.id,u.email,COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id=u.id WHERE u.created_at > '2024-01-01' GROUP BY u.id,u.email ORDER BY u.id; a cikin hanyar shigarwa tare da yaren SQL da aka sanya zuwa PostgreSQL, shiga sarari 2, da halin keyword BABBA. Fitar tana sanya SELECT, FROM, LEFT JOIN, WHERE, GROUP BY, da ORDER BY a kan layukan su na musamman; kowane ginshiƙi a cikin jerin zaɓe da jerin ƙungiyar yana samun layin sa da aka shiga; kuma nau'ikan ON yana zaune shiga ɗaya ya fi zurfi fiye da keyword na JOIN da ya keɓance ta. Tambayar ɗaya ta hanyar yaren BigQuery yana samar da fitar iri ɗaya a wannan shari'a amma zai kiyaye masu tanadar da backtick suka naɗa idan akwai su.

FAQ

Waɗanne yarukan SQL ake goyon baya?

Ƙungiyar yaren SQL ta rufe Ma'aunin SQL, MySQL, PostgreSQL, SQLite, MariaDB, Transact-SQL (SQL Server / Azure SQL), BigQuery, Snowflake, da Redshift. Ɗakin karatu na sql-formatter da ke ƙarƙashinsa yana kuma gane DuckDB, Spark SQL, Hive, Trino, Db2, N1QL, PL/SQL, ClickHouse, TiDB, da SingleStoreDB — zaɓar yaren SQL mafi kusanci yana samar da fitar mai ma'ana ko da maƙasudin daidai bai shiga jerin ba. Masu tanadar, kirtanin gaske, da masu aiki na musamman ga yaren SQL (PostgreSQL @>, prefix na BigQuery SAFE.) ana kiyaye su a zahiri.

Shin wannan yana tabbatar da SQL na?

A'a. Mai tsara yana sake rubuta lexical, ba mai fassara ba. Yana raba shigarwa zuwa tokens, yana amfana da ƙa'idodin tsari, kuma yana fitar da sakamakon; baya duba ko tambayar tana inganci ta ma'ana, ko tebur da aka ambata suna wanzu, ko tsari yana doka a cikin yaren SQL da aka zaɓa. Gudanar da fitar da aka tsara ta hanyar bayanan bayanai na gaske (ko mai duba SQL kamar SQLFluff) don duba daidai ainihi.

Me ya sa keywords na ta sun zama babba?

Ƙungiyar halin keyword tana tsohuwar BABBA, wanda shine al'ada a yawancin jagorar salon SQL da aka buga (dbt Labs, Mode, GitLab). Canza zuwa ƙanƙane idan ƙungiyarka ta rubuta select / from da ƙanƙane, ko Kiyaye idan kana son fitar ta kiyaye ko wane hali da ka rubuta. Masu tanadar ba a taɓa su — kawai an sake rubuta saiti na keyword da aka gane.

Shin ana lodawa tambayata a ko'ina?

A'a. Ɗakin karatu na sql-formatter da aka karɓa yana gudana a cikin mai bincike, tsara yana faruwa a cikin gida a kan na'urarka, kuma rubutun tambayar bai wuce cibiyar sadarwa ba. Buƙatun waje kawai wannan shafi ke yi su ne irin buƙatun analytics da tallan raba iri ɗaya da kowane shafi a kan tools.ultim8soft.com ke yi; rubutun SQL kansa baya zama wani ɓangare na kowannensu.

SQL da aka tsara ya daina zama jayayya ta salon da zarar mai tsara ya sarrafa shi. Kayan aiki yana gudana gaba ɗaya a cikin mai bincike, tambayar ba ta wuce cibiyar sadarwa, kuma tokenizer mai fahimtar yaren SQL ɗaya da ke iko da ɗakin karatu na sql-formatter npm ne ke yin sake rubuta.

Tsara tambayoyin SQL _

// Kayan Aikin da ke Da Alaƙa