SQL IDE Safari : Harlequin, in your terminal.

0:01all right hello everybody and welcome to another session of quackin code uh I'm Medi uh dat engineer and deal at Moder deck and today we're gonna discuss about SQL IDE and I was just saying our guest that it's actually an interesting topic because I haven't seen that much people uh talking about it but hopefully I think we have uh plenty of things to

0:29talk about and I have someone that actually worked uh on the open source SQL IDE we'll dive into that that will be uh the Hands-On uh part but uh first

0:41where are you everybody if you're uh online and with us I see there is at least four people watching us don't be shy and say hi in the comment uh say where uh you are from I'm based in Berlin and it's already pretty the evening uh here but I have uh with me uh

1:03Ted which is actually uh on the other side of the word and it's h it's lunchtime actually for you D that's right one o'clock just had my lunch so it's good to see you mie thanks so much for having me on no it's a it's a pleasure to uh to have you here so we

1:24actually met in person right in San

1:29Diego right that's that's right the Deep yeah that's right yeah it coal first time we met yeah what was what was actually your takeaway from this conference I'm DF him a bit before introducing yourself oh yeah I I had a great time I um I was there with one of the companies I work with called workstream IO and we gave a workshop

1:49that was actually super popular and and over subscribed um uh and that was about kind of how to use the the workstream project or product to um help minimize the annoying parts your job and I think in a way um that sort of tagline also works for DBT itself um I started using DBT oh gosh like six years ago now um

2:12and um yeah we were we were introduced to the tool from by Fishtown analytics which is the consulting firm that turned into DBT labs and um so we hired them very early on and um I wasn't I wouldn't

2:26have called myself a data engineer back then I didn't really know what I was doing we were starting to have to move more workloads into a data warehouse and DBT was like the thing that let us automate the annoying parts of our job and so it was fun to be back uh you know together with the DBT community at cols

2:43giving a talk that kind of was was specific about workstream but also um uh sort generally kind of summarize my thoughts about about DBT so I had a great time yeah no but that's uh I mean I've been also I mean I haven't been an early adapter because I remember we like like a lot of company built you know a

3:02DBT tool like for internal purpose as I was working as a data platform engineer building tool and back then like six years ago like it was really early project right because there was a hyper growth at the moment to the community and it get awareness but what what made you like bet on this projects such early on so at first I was a little annoyed at

3:26how early it was and we wouldn't probably have chosen the product we hired the the company so it was kind of funny because we um at first I was I was thinking these guys are pushing the software that they wrote and it's not really ready for prime time and I was sort of annoyed by all the rough edges

3:41um but honestly I didn't really have another choice I didn't have a team of data Engineers to build something similar for me and um our needs were small enough and you know we were basically migrating workloads out of like looker persisted tables into the data warehouse and moving from like a postc database to Red shift and um this is all

4:04you know 2017 technology right but

4:08um uh but but you know we the there was enough promise there with the tool that we kept using it even after Fishtown kind of was no longer engaged with us and then um as they kept investing investing you started to see this kind of Snowball Effect where after we got to you know version 0.10 or something like

4:27that it became a huge Force multiplier and and loved it and I um you know trained my team on it I started contributing to it I got a lot more technical myself partially just from being involved in that project and so it's um uh it it was a very cool experience and um it hasn't always worked out for me to pick to pick tools

4:45very early but um i' I've made some good bets in my career and um it's always been fun kind of being part of that Journey yeah I think it's also kind of uh a responsibility right uh to make good bets some sometimes like it's it's like some tools look shiny and you shouldn't like jump on every new tech

5:06but sometimes you can also make good uh um good bets anyway uh let's introduce yourself uh Ted what who are you what are you doing and what brings you around here I mean I already be SPO a bit but but yeah yeah um so my name is Ted coner I live in Boulder Colorado um I'm a fractional Chief data officer at a

5:32handful of um startups uh so among them are workstream that I already mentioned um which is making a a product for uh analytics engineers and data analysts and and people to help facilitate the communication around uh dashboards and other data assets um another company I work with is is privacy Dynamics and we U make it really easy to De identify or

5:56anonymize data sets so that you can use them for testing and analytics and all kinds of other things um so I I kind of found myself in this

6:07um particular job through a career as

6:12really primarily an analyst that's always how I've thought about myself um so I have an undergrad degree in um engineering but it's mechanical engineering actually and um uh I went from that into Consulting at ban and Company um and I really always thought of myself as like a business person and like a business process person um but

6:32then I jumped into startups in like 2012 and at a couple of different startups was kind of the strategy and marketing guy I was the data analyst um and it's really only been in probably the last um I don't know seven or eight years where uh the word data as like a job description started to both like ENT the

6:54general lexicon but also you know made its way into my resume um and and uh that was primarily at at a company called makespace which was a a full service like kind of tech enabled Moving and Storage Company um and it makespace I led uh the data and strategy teams for about six and a half years and that's

7:12when I first started using DBT and red shift and where I really improved my SQL skills I started writing python um and things got kind of a lot more technical from there um it's pretty crazy that you declare yourself initially as a business person and now you're maintaining like a technical open source project right yeah it's fun

7:33I mean um like I always enjoyed it coding I mean I I I took um like computer science classes in high school and in in college um and I always enjoyed programming um but it was never I never thought it was the thing I wanted to do all day every day and so I didn't pick a career as a as a software

7:52engineer um things would have worked out a little bit differently I don't know uh if how how that would have been but um I've kind of loved being a bit of a polymath and and being able to go technical on things but really like have all the context of what the business is doing um and that's how I find myself

8:09kind of having the biggest impact and that's the the thing that's most important to me is kind of influencing the the companies that I'm working with and um you know growing them into something great and having the biggest possible impact and so it's nice to have that kind of rounded skill set um in order to do that yeah and it's mentioned uh funny

8:27you mentioned a fraction CDO right and actually fractional C Level is the first thing I heard like not like so far long ago like two years ago and I heard like CFO fraction fractional CFO and I was like why would you actually need someone not fulltime maybe you can a bit elaborate on that yeah yeah the idea I think

8:53started with the CFO actually um and uh

8:58the uh uh it's sort of rooted in a need for two things so as an early stage company um you want to have experience around the table and it's nice to have um you know people who are experienced managers and you know people managers and know how to grow and build a team it's nice to have people who are familiar with technical

9:18subjects and have like deep expertise in those subjects so you know for a CFO that might be accounting or something like that and kind of talent early as a as a startup it helps make sure that you're organization doesn't develop bad habits um on the flip side it can be very expensive to bring in someone with a lot of experience um to an early stage

9:38company which is typically you know poor on cash so you to end up either giving away a big chunk of equity to attract these people um uh or you know you burn through all your cash very quickly um the other kind of challenge for a startup trying to like attract and retain someone with a lot of experience

9:56is just that like the job isn't that hard yet um so you know if I worked full-time for any of my clients like I wouldn't just be doing data stuff that I know and love I would also be doing you know frontend web dev and customer support and all kinds of things that are kind of both like lower value because I

10:14don't have experience and a skill set in those areas um but also just not as interesting to me personally and so it's um being able to be kind of part-time at multiple companies you kill all all sorts of birds with one stone which is that it's cheaper for my clients it's more interesting for me um it allows me

10:33to kind of uh keep tabs on a handful of of different um companies rather than having to like make one big Equity bet on on an early stage company so um it's it's really a great situation and so I think we're starting to see that fractional idea move out of Finance you know with the fractional CFO and into um

10:51some some other departments or teams where um you know you start to have a cro who's there just to sort of you know provide guidance to some early salese or um you know a CMO who who helps with you know branding and high level strategy but leaves the details to somebody else and so it's um it's a really nice

11:09balance and it's something that um I think we'll see a lot more of in the next decade or so yeah no it's uh it's

11:17really an interesting Trend and I I I could feel that uh I've been asked like for example you know who should be the first uh you know hire or startup reaching out to say hey can you look a bit at this design like a one-time you know uh advice and it's exactly as you said it's like it can be expensive for

11:35them but at the same time if they get um you know experien at senior advice on those things uh they can really prevent to accumulate certain technical deths right ex because they exactly they didn't make the the right decision especially like for a lot of SAS product they they often not super you know data aware like those come kind of a

11:59afterthought I mean just in general in any company size but initially when you built your your you know your SAS service typically you're gonna have some metrics on you know Gat server but then there is not really a data strategy around this right yeah exactly and something as simple as having a spreadsheet that lays out your tracking plan for your SAS app

12:24um you know I've built a lot of those spreadsheets over my career it doesn't take me long to do it and it's something that's just super valuable to have as like a road map for the engineering team and then you know before you have a lot of usage on your your product you can sort of take your

12:37time adding in that tracking and we can take the time to uh develop the reporting on on just the metrics that matter so you know I can do it in a way that's super efficient and um but but sets us up for success in the long run as an organization yeah um no that's fantastic coming back to SQL IDE so what's what's

12:58what's your first contact with SQL ID so um I guess it actually comes back

13:06to dctb I was working with a few different clients and um starting to have a little more exposure to like a data Lake architecture and starting to work with more paret files and I was using the duct DB CLI to like query paret files that were um either in an S3 bucket or on like a remote server that I

13:26was sshed into um and I I I love the duct bli I think it's this really cool little piece of software it's super powerful um when was it by the way sorry your when was it what's the timeline where you started oh this was uh like nine months ago okay okay so pretty early I I was thinking if you did

13:46another uh you know amazing bet because you bet on DVD okay um yeah so um anyway so I was

13:56working with all these big files and you know the duct TB CLI I found to be great if you have a simple query um you have a just not that much data that you want to sort of examine manually um I I end up you know as a business analyst I end up writing these horrendous you know 50 100 300 line SQL

14:19queries um and I also end up wanting to return a lot of data back from the database into the clients that I can just look at it I'm a huge believer of like you got to just look at the dat to really understand what you're working with um and when I was doing that in the duck DB

14:34crating like you know it it truncates the number of rows it prints in the terminal it you know will just truncate columns and not show you any output from you know those columns if if you select too many um and I was uh I was like man I wonder if there's a better way and I um there are you know obviously other

14:51ways to interact with uh with duct B um

14:56and I was thinking about oh maybe needs to spin up a different SQL IDE um uh at

15:03the same time I was sort of on Twitter and I was learning and hearing more about textual which is a a framework for making um apps in your terminal so that's actually what what Harin uses but um I was sort of following along with text rol and I uh was literally in the shower and had the thought of like huh I

15:24wonder if anyone has used textu text tool to make like a a different duct DB like CLI replacement um and that was um yeah that was nine months ago and um part of the nice thing about my my job being kind of part-time is I have a sliver of time left over to do with other stuff and so I think it

15:46was you know the next week that I had a little bit of free time and I wrote the first couple commits of um of harlequinn um and then I push those to um uh to

15:57GitHub um uh some s of friends of mine in in the DBT Community um including uh uh

16:08Gwen um uh she like started the repo like day one um like before I even I think was able to to Star the repo she was like on it and so um I was like whoa

16:20like other people are excited about this right and um I so I I sprinted towards like a very crappy MVP version of it shared it with a handful of people in the DBT slack and got like overwhelmingly positive feedback and so I was like okay this is this is something worth kind of investing in and so um I then spent about three months um

16:43you know part-time with you know my other jobs also going on but it took me about three months to get to what I called version one um and um version one

16:53again it was all about duck DB right it was just a better duck DB CLI replacement that worked great great for kind of you know more analytical workflows um and uh so I launched uh 1.0

17:06with a with a video and um it ended up kind of taking fire so um um uh yeah so now you're yeah no worries so now you yeah you're actually at 2000 stars on G up like congr like in in nine months basically that's yeah in nine months and and a lot of that she's been in the last like couple of months so

17:30I've been I've been on the front page of Hacker News Now twice um uh once for the 1.0 release and once for a more recent release um there's been just like a lot of great feedback um you know some people in the community have been um kind of uh amplifying it and and helping me um but yeah it's been it's been a

17:53really fun ride so definely I yeah yeah sorry no no it's no but it's great it's I mean obviously you're passionate about it because like that that takes sometimes right to to maintain and P things uh but I have an important question did you make yourself the website I did yeah wow like I'm impressed like I would I couldn't come

18:17with like just the color palette and I think it's like it's um it's shiny without being you know too aggressive uh oh appreciate it yeah really

18:31I the color palette came from uh like one of those online palette generators you know I just was scrolling through sort of color palettes and um I I I landed on the name Harlequin like very early on uh which um you know is that

18:48that's an an old word it's you know a French word it kind of means like a Jester or comedian but um there's also a kind of duck called a harleyquinn duck which is a colorful and and beautiful duck and so when I was first kind of building my uh you know duck dbcli replacement that the idea was like I'm

19:05going to make a a more colorful and beautiful uh you know CLI thing so I wanted it named for like a colorful and beautiful duck um yeah no that's a that's that's really really great uh color like you have you have really various skills also internal front end and not just doly data but it's a lot about in for me right so

19:29like um I I am I'm a crappy web dev and I've learned a lot just like building this up from scratch and that is more satisfying to me than um like I have another open source project which is SQL format uh which is SQL fm.com and um that is a um a docusaurus site um

19:50and it was always just kind of like frustrating and annoying to work with like a framework like that and I just felt like I was fighting the framework but not actually learning anything about JavaScript or anything else and so for this one I just wanted to to start over from scratch so it's um it's a spel kit

20:04app but it's um you know mostly kind of hand hand rolled uh framework and and you know I uh it's been fun kind of learning um and and learning python just as I've been developing the tool too um it's been this whole the whole reason I do this is an investment in in myself in the future yeah no that's nice we have a

20:24nice comment uh I don't know why it's a LinkedIn user but uh from the LinkedIn it's LinkedIn being LinkedIn um so you have people commenting in the chat that they enjoy the website coming back to my question to SQL ID you mentioned like uh Arin was

20:43uh you know the reason initially was the dug DB CLI kind of limit uh but just out of curiosity before that um how did you

20:53use SQL like what's common you SQL ID used to uh to try or to use within your business or not yeah I I've been a data grip user for a long time um so especially when I was using um red shift more um data grip was like my daily driver um now I find that the cloud data

21:15warehouses mostly have like a good enough sort of query writing experience so yeah I end up spending a fair bit of time in like the snow site console and for snowflake um and just you know going straight there um yeah that's that's a really that's really interesting I had the same thought so yeah basically I I started uh you know my career in data

21:42with the Microsoft stack and there was a SQL Server uh management studio and um this was released already in 2005 so it's pretty long time ago that kind of like the first ID that at least took off um but I had the exactly same F news that actually today there is so much inv bided you know SQL

22:10interface and I'm wondering if um still

22:14people are actually using a lot of SQL ID people in the audience watching us are you still using a lot of uh SQL ID

22:23and if you are which are your preference we talked about data grip there is also so uh the Behavior Uh which is a pretty

22:32big success in the open source but I'm really curious because that's myself most of the time now indeed uh I'm going to just use the you know buildin um SQL

22:44interface and as you said like they they have like those uh provider like would it be snowflake or big query they they build specific features like you know Big C has this uh number of um

22:58uh data scan right that you can see as a preview Y and I don't think it passed I remember like least a couple year ago it passed to the client or whatsoever there is somehow a way to work with data grip or the behavior but is I I assume like they they would not make too much effort

23:18you know to support those SQL ID what

23:23what's your what's your yeah so there so there's a big query adapter for harlequinn now so you can use with big query um uh that was a

23:33um uh something that was kind of contributed by by a Community member um Josh Temple who um you know I'm super grateful to but um I actually think that there you can get that like you can analyze a query and get the rose scanned um and thinking about how to possibly work that into to Harley Quinn also but

23:52um uh there are uh you know a lot of these warehouses also have like a pretty good API and a pretty pretty good python API which makes them easy to work with um and easy to build these adapters for Harley Quinn for um uh but it is nice just kind of having that that IDE experience um right there for for the

24:11end users to jump into I know I personally like I like um not getting logged out every you know six hours or whatever like I do from Snowflake UI um

24:24so it's nice to be able to like persist some credentials locally and um and not worry about that so much but um yeah it's definitely it's definitely easy just to go to you know snowflake.

24:35comom or whatever and start heing yeah so uh Victoria say hi Victoria by the way uh hi again I had lunch we have lunch together today um yeah so she also

24:49agreed that actually a lot of invited SQL um uh interface are actually uh

24:57Prett pretty good now uh but I I guess like if you work with multiple different database um and I think it's still as long as you have you know one plus something then probably like an ID will help you right because you need to be able to switch directly uh rather than having another tab or login um are you know uh different story I

25:28think you know if if uh again the Genesis for Harlequin was like a better duct bli and so I think if you're looking if you're using duct Tob CLI or PG CLI or the sqlite 3 um you know

25:42binary all the time um you should really give Harlequin a try because um it can sort of run in any of the environments where those programs run including like on a remote Ser server that you connect to you know bya SSH or whatever and it's um it's a big upgrade in most cases um especially if you have more of like an

26:01analyst workflow where you're writing and refining queries and looking at data and things like that more than doing the the DBA kind of workflow um harlequinn is is designed for that and works with um all of those databases now so you can connect to dctb sqlite uh MySQL postgress uh SQL Server um and uh and do

26:22everything kind of in the terminal you know very keyboard friendly very workflow focused um very fast and so that's that that's kind of the goal is more to compete with those command line tools than to try to compete with like the the data warehouse uh you know browser based IDs no I totally hear you I have a couple of tools that I'm using

26:42on uh on my terminal and this is just uh faster and it's and it's corresponding to my workflow too I'm I'm an heavy keyboard uh you know workflow user um

26:56I'm not using neovim but I do have the binding here and there um so so yeah that's that's definitely make sense for the specific user last thing uh before jumping into actually aring U I was doing some research um to two thing actually the first thing is like do you know how much SQL ID there is out there

27:19on average like any any any any good number of like available SQL idid that's being out there how many different programs would call themselves a ID I have no idea I would think you know probably dozens have actively maintain ones or more um yeah so I I'm

27:40curious what the audience think but like for me I I didn't think it was uh a big business because we mention like the the big one data grip the behavior and there is the the Microsoft switch right uh but

27:56actually there is is more than 50 SQL ID

28:02this is a study from 2020 so probably but it was updated actually uh this month so I guess it's updated there you go um but it's it's pretty crazy like

28:16there is a lot of them yeah and there is a lot of them paid so that's also surprise like you know we were talking about um user preference and so on but then there is also the question like would you still you know go and pay for that um because intellig can be sometimes expensive license um but but

28:38yeah so there is a bunch of them I couldn't find uh I looking on this one because probably they haven't looked on

28:47the longer I guess yeah yeah but you could think that other thing is that uh what you should definitely do uh the other thing I wanted to talk to you is that so on the dgdb documentation website there is um some guy for SQL

29:06editor around how to connect with uh

29:11with the Behavior Uh so I think it's totally forfeit that to make a a small guide to how to connect to Al for to have it on the on the ddb documentation website so funny story about that uh yeah yeah there used to be more ID no there used to be more ID on on the duck TV dos including uh including harle

29:38actually if you if you rewind the clock in the the version on the top right from Dev if you go back to like version 0.8 you'll see you'll see listed there so yeah talk talk to your friends over at the duck DB foundation and and let's get a Harlin back on there I will I will this is uh this is interesting um I

29:58guess there is pro count on uh pushing multiple ways for a new user that might be scary but at the same time I think if it has a a right place in the documentation it's it's worth it so thanks for bringing that back uh to me I'll be sure to uh follow the lead um anyway we are already um half time so

30:20we're gonna go dive into uh Arin so I actually didn't play that much so I'm I'm a new right uh so fortunately we

30:31have uh uh Ted here uh to to help us let

30:37me just uh bring the chat back here so that you can also see um I have we have a question what do you think about inous setup with DBT post gray wow that that could be a podcast by itself so um so we'll focus I

30:56can take some time to to maybe just after we'll we'll dive but I just want to see if there is any uh question or Point regarding yeah big query has um

31:07now cross support database join so it's true that you could start to um you know build one single entry

31:17point and assuming that people have big qu only installed c a different place yeah but that's still sounds expensive exactly I think that's a lot of requirement back in but uh but yeah so

31:32anyway we going to go to uh to the

31:36terminal right uh so actually I have already installed but uh it's just a

31:44people install right yeah um and U

31:48that's that's basically it um what else

31:52I think yeah be careful I think there was an issue I saw regarding python tree 12 12 that's right yeah yeah so just be

32:02mindful yeah yeah that traces back to dctb unfortunately um there aren't binaries for the most part um orb on python 3.12 and so uh it'll take you you know a thousand times longer to install harloc Quin on on 3.12 than it will on any other python after 3.8 uh so we 3.8 to 3.11 yeah so just just uh FYI if you

32:29install it just make sure you're not on the bleding edge but anyway I always think like the 3.2 was out like uh in

32:38November at least G right I'm not mistaken it's been three months now or something like that yeah and I think usually you should at least give it six months to migrate to St unless like you're you know uh just a fun side project but I would be if you want to be safe just like take 3. 11 and and

32:58migrate after you know six months to a year it's it's pretty good every library has been pumping their the dependency or thing isue um exactly so where do where do you usually start when you show um do if I just type without anything actually exactly so if you just type in Harlequin you get an inmemory DB session um and um

33:23so in front of you you have a query editor there so you can write you want so you know we start with select one and then um press contr and

33:34enter yeah and I think you have actually the shortcut below so I just uh I just F

33:42so so here what happened is that when I'm launching I'll look in what is launching is a Doug DB in memory right that's right yeah okay and so that's why I can uh I can directly um run

33:58that that's right yeah and over over on the left side there you see the the data catalog just say memory so that's an an empty um database right now you can create a uh a table there so why don't you go ahead and um um you can yeah instead of select one create table you know Fu as select one and then um you'll

34:20see that pop into the the data catalog and uh yep is there um and um

34:31and by the way I think so do you have you don't have theme binding or do no it's it's it's it's vs code Microsoft Excel inspired bindings um so you can

34:45there there's a handful of commands in the footer there and then yeah F1 will always kind of show you um all the Bindings that are available yeah Vim bindings is a is a highly requested feature um I'm uh waiting on some Upstream stuff to happen before uh before I can support that though yeah yeah yeah yeah so no indeed

35:09it's um it's actually the the first thing I want to to do you know because like as I said like I'm I'm trying to put VI as much as I can on different plays in the browser or and the thing and uh and yeah but at least like the I mean the mouse is working right it's terminal but the shortcut on the mouse

35:29is working so yeah exactly yeah so so Mouse is fully supported you can even like click and drag in the query Editor to highlight text um for example um you know okay yeah the the so

35:43the KE forch works and yeah as like a vs code user myself I've tried to sort of map you know VSS code sort of experience in terms of what happens when you press either key bindings or you know you can even double or triple click your mouse to hide highlight you know a word or the whole line um and those things are all

36:00just kind of ripped from the the vs code experience yeah okay now that's um that's nice I've seen that you had uh if I'm going back to our you had a beautiful team how do I put them actually those on oh sure so um you can where do they come from actually yeah so um there's a python

36:24Library called uh pigments uh Ms is like a library you could use to create um a language parser um but they also have uh themes built in for like syntax highlighting different um uh different languages with with you know different color themes and so we support any pigments theme or style yep um there's a long list of them some of them I think

36:47are prettier than others but um you can configure your theme when you when you start harlequinn you pass in the the T or theme option um so I think if you want to try that um there's one called Nord dark which I think is quite attractive so Harlequin T Nord um I think it's Dash dark or darker sorry

37:10Nord Dash darker uh let's let's uh check

37:15uh Nord darker yeah all

37:23right um now you have the same same experience but it's in you know sort of muted blue tones if you don't like my sort of yellow and and pink and and blue in your face you you could have an alas for Arin that switch every time the team right or after second every week I'm getting a new team yeah for sure no it's

37:46uh it's nice I didn't know about those python Library actually it's pretty Endy uh to bootstrap multiple team um quickly

37:57right yeah it's great it's great um and I found it through again through text rule there's some sort of native support in text rule for some pigment stuff and that's that's because Tex texol shares its DNA with another python Library called Rich um they have the same Creator and um uh Rich it sort of integrates with pigments um to syntax

38:17highlight text so um there's there's that's sort of the reason that I chose the library so this is uh just showing

38:25um moving the chat for a sec but we yeah that's like quite a big Li react to T and I remember actually that I never built something but I had um an internal project that was using that and it seems like it's it's pretty fast to start I mean depending on what you want to do but it's seems like a a pretty decent

38:49framework if you want to build uh guwi within the terminal right yeah it's it's um it's kind of an amazing piece of software it's it's pretty complicated because it's sort of a browser and a uh framework and a component Library

39:07like allinone um yeah but if you have

39:11done any front-end web stuff and you know any like react or anything like that you would start to see a lot of parallels in textual and um okay there's a lot of nice apis and textual not just for like drawing characters on the screen but um there's nice apis for um creating workers that you know do work in a thread that doesn't block the

39:30main thread um you style all the components and textual with CSS um and so it's it it borrows a lot from like web development and and makes sort of terminal development feel more like web development amazing all right so what I want like to do uh because that's the thing I wanted to speak about is profile because this is I think in my opinion

39:54SQL ID shine today is that that you want to connect fast into multiple different you know uh uh type of database or instance of database right if you have something in staging and something in Pro in in in production and quickly switch out I think uh that's actually a really great use case because typically if you working with big query and you need to

40:18use different project and you have multiple projects like having the the

40:25that's where like the web interface start to get some limits right so if you want to have them in parallel for example and so on you need to have other tab or like Chrome tab have like different instance to have different login so anyway um so let's try that like um I want to be able to uh log bigquery let's say um so I

40:52have I do have I think the gcloud install yeah so I guess I if I look at

41:02the documentation you have some section about that how to unicate yep um so uh Haron will use

41:12whatever kind of default um login credentials you have stored in gcloud so if you're already logged into a project in g-cloud then Harlequin will just use those credentials from the g-cloud CLI so you don't need to type any credentials into Harlequin or anything like that um so to get started you need to install the harlequinn big query

41:33adapter um I don't know if you um so you'll you can do that I can just talk you through it too if you if you want to do that but yeah let's go so since you're already installed with with pipex actually you're going to want to take the line above that which is pipex inject Harlequin Harlequin big query um

41:52yeah so it won't pipex doesn't like that because you already have Harlequin installed so you're going to do pipex inject and then um uh so that tells pipex to like add an extra program uh no it's just instead of pipex install it's pipex inject it's a different command um and that'll that'll install and then instead of the Harlequin with the extra

42:18big query you're going to want to do Harlequin Dash big query because it's a different um it's a different python package so line right there yeah that one yep all right so now that's going to um just take a second so again this is installing a a different python package um written by Josh Temple who um and uh

42:41so now though harlequinn itself will find that as a plugin and you can um connect to Big query just with Harlequin Das a for adapter and then uh the word

42:55big query yeah um that's assuming I have

42:59uh gcloud credential that's right yeah so and I think you need a a project name and a region as well um yeah let me just uh

43:10check out uh quickly that so that I don't share anything and don't get troll after now while while you do that I'll uh I will plug mother duck is actually even easier to connect to because the you know duct DB itself handles the connection to mother duck and so connecting to mother duck is as simple as just typing in Harlequin MD colon and

43:35then you'll pull up um all your mother dock databases yeah yeah but I want to do the r stuff

43:44first fair not uh so I think I I run um

43:52I think I run the the Google application login and I think it's pointing me to a project uh I'm not sure which command is again uh project list it's been a while not using that much um but let's let's let's try um let me bring back theen and

44:15if you ever get lost here you can do harlequinn d-el um and that will um uh show you

44:22kind of all the commands that are available um both for Harlequin and then for any installed adapters so there's going to be a block there for the big query adapter that'll show you that you need to feed it I think it's a a project and a location option so if I do this is

44:38going create uh a config file or just gonna ask me yeah okay it I think you need a project name and a um an location

44:49maybe I need specific uh permission yeah so when you when you go back to if you go back to the command line um if you start Harley Quinn with a D- project argument um so yep big query

45:05so a big query and then dash dash project and then whatever the name of your your project is I think that's what it needs yeah all right uh but I think I think if I do that basically is pointing to um

45:21this one and I think it just need uh a specific permission because I think it's login uh with uh with my user that's

45:31that yeah that's possible um so yeah I think in the docs there's the authorization you need can I can I use a service account you can yeah I think so um

45:45that's right so so that last one it's saying you either need admin data viewer or metad data viewer I think yeah okay uh I don't think I have this at least on the permission uh I'm working but I think if I pass do you actually look at the Google uh um application credential environment when launching or do you specifically

46:16look for the defa credential you know I don't again this is you're talking about software that the Josh wrote so I don't know exactly dets is Josh online that's true that's I know I know it works when you pass it a project name I don't know what happens when you don't pass it project name I didn't yeah yeah

46:33no no no that's um but you know do you have a local duct DB database or a parket file or something like that because we can yeah yeah yeah sure let's let's go let's go there we have uh we have uh we have there but I think it's interesting so um just use the I'm not

46:51sure if I I'll try after if I I can actually uh use the service account but otherwise just make sure that you have the right permission as an user uh so there permission here and just authenticate and it should be good to go let's dive now into uh local ddb um so actually I have uh right here

47:16um I have uh a dub database and I have a bark file um so we can if I do are

47:27lookin Andress the path of the of the database or do you so it's yeah so if you want to run like just an inmemory database to select from that file I would I would do harlequinn um if you're going to be working with files you can you can pass the show files option and that will add files into the data

47:45catalog so oh um okay so why don't you do harleyquinn DF and then period for the to show the current directory and go ahead and yep and now the data catalog on the side there um you see the files um in your query editor you can you know select you can type select star or or attach yeah that works um

48:10depending on what what we're working with here um from and then go ahead and

48:16double click on one of those files on the left or and Ah that's very nice

48:23yeah and now you know control enter again to run that query um and Harley

48:31Quinn uses uh Arrow under the hood um you

48:37might need to put your cursor inside of the because you put a semicolon in it might be just I think it's executing the the blank query after that one yeah there you go try again um yeah so so we use Arrow under the hood um which makes selecting from a park file super fast because uh dctb doesn't even have to like bring it into

48:59memory it's just all is operating on the par file directly because it gets um uh read by Arrow so um like dect Tob found what 3.8 million records there and we're showing 100,000 of them in that data viewer or in that results viewer and it's it's super fast you can scroll through that 100,000 results you can scroll right and left and it's going to

49:20feel um really fast and snappy to to De all that data if I'm writing uh another query can I select uh and control run it will work or do you have to delete or create another pen um so you could go you could do contrl n for a new tab or a new buffer okay okay and that's how usually

49:44works yep okay um you can also one other trick while we're while we're looking at this all this data in the results viewer um uh once you click back into the results viewer and then and then press F10 and F10 will go into full screen

50:02mode so you have um a lot more room for activities I guess to to view your your data um and then you can you know F10 again to go back to the um the query editor you can also similarly full screen the the query editor um which is nice if you write heinously long queries like I do yeah okay and so from here

50:25here I can attach database also that would work that's right yeah and uh and

50:33yeah okay um I want to try um with a specific profile so talking about uh the profile and let's say let's try let's try mother duck uh how the profile setup is working actually hey looks like looks like Josh is enter the chat actually speaking of Josh Temple um thanks for all your work with harl big query also

50:55has a good a good tip for you which is uh yeah you can have multiple queries in in the same tab um yeah it'll run the one your cursor is over if they're separated ah thank you yeah oh that's that's great uh Josh thank you for for joining uh it's like Beetle Juice I guess we talked three times about you

51:13and you're just popping up it's amazing um so yeah one last thing I want to try is basically just the profile because so that people can switch into different profiles so let's let's create uh configuration file and and connect based on that uh where do I start so the easiest way to get started with that is running harlequinn D- config and that'll

51:35walk you through um a config wizard for

51:40um for any of your attached adapters so this is going to create a config file at Harlequin um yeah so then you you just give um your uh your profile name so um

51:55you know this could be Dev orad or you know whatever it might be um yeah let's said Dev uh mother deck and then select yep and then so for

52:08mother for example this would be um you know that whatever argument you would usually use on the on the command line for Harley Quinn so this could be MD colon to connect to mother duck um and then I have normally uh my token

52:25available so you should be able to fetch it I guess uh yeah and if you don't it should it should like force the login flow the same way as if you start you know the duck mother duck argument yeah okay so MD colon over here yep and then

52:43um now you choose a theme so um you know yeah so you know nor dark or you know whichever uh Native is a nice one too uh kind of like native okay that's right and oh okay many rows

53:00that's yeah depending on the adapter you know you may want more or fewer rows to be returned uh by Harley Quinn kind of by default um and then um you know this

53:11is basically that files argument so do you want to show files in your um uh in your data catalog so you can okay uh enter like a period here if you want to include files from the current directory um every time you open this profile

53:27um uh we can also show similar to local files we show cloud storage files so if you have an S3 bucket pull of parket files you can show that as like a directory Tree on the leand side of of Harley Quinn um so it will be at the same place then the there was uh if it's an open bucket would work I guess uh an

53:47open bucket would work yeah and then any um any private bucket you would just need to be like kind of logged in through the AWS CLI just like the kind of kind of same experience you just went through with the gcloud CLI for um uh for the big query adapter uh let's try uh I think this one

54:07uh at least this one this file is public

54:12I'm not sure if they're going to be the the right permission to to list but uh but let's uh let's try uh like this I don't have to quote them you don't yeah that's fine all

54:28right which of the there's there's a bunch of options for the duct DB adapter specifically so yeah um uh you know if you wanted to let's say put in a mother duck token here um you could use the MD token option or if you wanted to run mother duck in SAS mode you could um uh you could configure that here um each

54:49adapter have its own set of options and this will store the token in the file then I guess in the file yeah in plain text so maybe not a great idea but you know uh yeah I mean I normally is is picking up uh from otherwise the mother token environment right right exactly yeah okay so normally I should do like I

55:11don't but if you wanted to let's say install extensions or something like that and make sure that those were always there you could um you could configure that here and put that in your profile so that it runs every time you start harle so do I actually have to pick something if I don't want to speak uh no just just press enter to to skip

55:28through this um okay uh and then this is asking you if you want it default profile so if you if you have a default profile if you just run harleyquinn bear with no arguments it'll it'll open this profile um otherwise you have to specify the profile name with the the P option um so harlequinn p and then the profile name

55:50okay cool and that's just a little preview of the um config file that we're going to build for you um so now it is in the it's actually you might have ls- because it has a dot in front of it um okay uh so should be it should be a harlequin

56:13doo in your current directory ah it wasn't the directory I was yes CSS yeah okay s right so then we

56:22were here uh so it should be there there y okay and you can you can put that in your project directory you can also put it in your I think it's the user home directory we'll we'll find yeah that was and then we'll that was my initial instag so yeah and then we'll we'll we'll merge any profiles we find too so

56:42if you have some in your user home directory and then you add some more in your project directory you can um we'll merge those and and you can select from any profile it would find cool great uh

56:53so normally if I don't specify anything let me just uh check uh first that I

57:01have my uh I just delete you um my environment

57:07set up with the mod do

57:12token uh and I do

57:18have

57:23so all right okay yeah so we are missing that the S3 extra so that's you know the same thing we just went through earlier with the big query adapter you'll just have to um either inject boto 3 or reinstall horal with the extra for that um bucket to show up I want to do that because the the the integration is

57:40working I actually this is my uh Cloud database uh from under deck yeah exactly

57:47and so if I do for example I had this

57:52amazing uh so so if I do this as said

57:57Josh I don't know how you're going to need a semicolon between the queries

58:03yeah

58:09uh uh so that you you um you're selected

58:14from a table now so you actually don't need quotes around it but you do need a semicolon like between your your first and second statements because those are ah okay this is the thing I want I wanted to know is that if he going to so now now it should run

58:33uh yeah you you just you've quoted the table names you just need to unquote that um and then you'll yeah yeah yeah okay I see is because so this is the kind of the thing where if you run and that's why you use multiple tab usually to not get confus uh of those um all right so here

58:54here we have the amazing list I showed you earlier with all the SQL ID are free or pay and which database are supporting so uh that's like the most amazing uh data set ever um but that's working pretty nice I want just to finish because I'm curious now uh to move forward with the uh S3 so I need to do beep inject

59:20something like that uh and then just I think uh no it's just Botto through it's just the Amazon um uh Library

59:31so an extra yeah Auto three and then it should work yeah okay let's try again so logging to M Dock and also

59:48accessing doesn't like your doesn't your bucket uh yeah because he's trying to uh logging from um from my credential SSO uh you might have to blow out your

60:05um yeah your profile or something because we're just going to take whatever is active in your AWS CLI profile yeah yeah oh that's fair uh with Ling so let me just log in quickly um good news is we show you a nice error message when that happens so I mean that's the goal right there is stuff that works there stuff that

60:30doesn't work it's alive and I think it's also bring more the reality from the from the user um I think it's a you should probably have maybe somewh error message for like people trying with without the inject because that's was not super straightforward to I mean when you tell me initially right that I need the extra package to install uh um but I guess if

60:56you run do you have a warning to say hey please install the big query additional package yeah it it pops up in one of those little error windows with the command you need to to run but we didn't um we just kind of Click past it last time okay okay I see but fair feedback I think I I wrestled

61:15with whether or not so I kind of bundled that with the um with the main package it's hard to know what to make and what to build in hey there wow that's amazing so you get basically we are alling a SQL editor and also a nry file explorer yeah and if you if you double click on one of those or press

61:35control enter there it'll um it'll put the whole path into your editor wherever your cursor is so um yeah it makes it makes it easy again like especially when you're using duct TB to just select from this file that's an S3 um yeah I think you yeah I think you inserted that right in the middle of your database name but

61:55yeah yeah inde let me uh let me grab that it's because I'm a I'm a I'm a messy person so let's do that on uh and I need the from or I get yeah you need the from and you need to you need to quote that string but

62:12um ah oh yeah contr e is not uh yeah the

62:18End Key would work or the control right okay I see all right so here and we have a 4 or three so this a permission thing and that's totally fair uh because on the RO I don't think I have all the permission uh for for that uh but still super impressive to um because we're already four minute ahead um to have uh the

62:43database uh here I'm I'm seeing the cloud database but I can see also my local database yeah and with three we created a profile um so this profile

62:57is uh around here and it can using the

63:01wizard was pretty straightforward and we can have this in the home directory and pointing when launching AR looking I guess yep yep so

63:12uh so yeah that's that's really um uh great stuff I'm glad that I didn't spend uh too much time because I knew that there was the expert of Arin joining right um but yeah that was fun I actually have no idea about the S3 file explorer I think it's a I think it's a pretty neat thing I wanted actually at

63:35some point build a a kind of a CLI in the terminal to to just have a quick Explorer because when you list you know yours CLI to list is not super hand if you need to quickly um navigate through

63:51things yeah it's it could be tough um and you you can run um you can with Harlequin you can pull up a single bucket or a single bucket with a with a prefix um for for a key a specific key or you can um run S3 with the all option and then we'll we'll pull in all S3

64:10buckets that your user is authenticated to um to find so um I definitely the you

64:16know again like being an analyst for 15 years I kind of get the workflows and it's been fun to build a tool that that just works with my with my workflow um and fun to see how it works with other people too no but fun it's a really great project so for the folks uh uh joining I'll put the link in the description of

64:35course but we have seen it's AR so you just I think if you Google it where are you ranking at the moment idea haven't done that yeah I guess if you put Arin G up

64:47uh probably yeah if you up Al can get up your first so okay that's uh that that's that's a a good deal Dad thanks again for for joining and uh I wish you a

64:59great day because it's uh past learn for you and for the all the others uh see you around for another quack and code with another guest will be announcing it soon um check it out our YouTube channel and other things to follow up see you thanks so much mny this is fun um and yeah if anybody has questions you can

65:20find me on on LinkedIn or the DBT slack and all kinds places so please do reach out I'll put all the links uh everywhere I'm sure people will reach out awesome see you all right bye