avatarRemington Write

Free AI web copilot to create summaries, insights and extended knowledge, download it at here

2649

Abstract

.status <span class="hljs-operator">=</span> T.status <span class="hljs-keyword">WHEN</span> MATCHED <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">SET</span> M.hoge <span class="hljs-operator">=</span> T.hoge, M.fuga <span class="hljs-operator">=</span> T.fuga <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">NOT</span> MATCHED <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-type">ROW</span></pre></div><div id="835d" class="link-block"> <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax"> <div> <div> <h2>Data manipulation language (DML) statements in GoogleSQL | BigQuery | Google Cloud</h2> <div><h3>If you use on-demand billing, BigQuery charges for data manipulation language (DML) statements based on the number of…</h3></div> <div><p>cloud.google.com</p></div> </div> <div> <div style="background-image: url(https://miro.readmedium.com/v2/resize:fit:320/0*zGNWuQ5watu2B-jf)"></div> </div> </div> </a> </div><h1 id="82fb">How to improve the MERGE efficiency</h1><p id="6c6c">We have three options to improve the MEREGE efficiency.</p><h2 id="d5db">subquery filter</h2><div id="0a3d"><pre><span class="hljs-keyword">MERGE</span> dataset.master M <span class="hljs-keyword">USING</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> dataset.temp <span class="hljs-keyword">WHERE</span> _PARTITIONTIME <span class="hljs-operator">=</span> <span class="hljs-string">'2018-01-01'</span>) T <span class="hljs-keyword">ON</span> M.id <span class="hljs-operator">=</span> T.id <span class="hljs-keyword">WHEN</span> MATCHED <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">DELETE</span></pre></div><h2 id="ca7c">search_condition</h2><div id="23b9"><pre><span class="hljs-keyword">MERGE</span> dataset.master M <span class="hljs-keyword">USING</span> dataset.temp T <span class="hljs-keyword">ON</span> M.id <span class="hljs-operator">=</span> T.id <span class="hljs-keyword">WHEN</span> MATCHED <span class="hljs-keyword">AND</span> M._PARTITIONTIME <span class="hljs-operator">=</span> <span class="hljs-string">'2018-01-01'</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">SET</span> id <span class="hljs-operator">=</span> S.id <span class="hl

Options

js-keyword">WHEN</span> MATCHED <span class="hljs-keyword">AND</span> M._PARTITIONTIME <span class="hljs-operator">=</span> <span class="hljs-string">'2018-01-02'</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">SET</span> price <span class="hljs-operator">=</span> S.price <span class="hljs-operator">+</span> <span class="hljs-number">10</span></pre></div><h2 id="8914">merge_condition</h2><div id="78ee"><pre><span class="hljs-keyword">MERGE</span> dataset.master M <span class="hljs-keyword">USING</span> dataset.temp T <span class="hljs-keyword">ON</span> M.id <span class="hljs-operator">=</span> T.id <span class="hljs-keyword">AND</span> M._PARTITIONTIME <span class="hljs-operator">=</span> <span class="hljs-string">'2018-01-01'</span> <span class="hljs-keyword">AND</span> T._PARTITIONTIME <span class="hljs-operator">=</span> <span class="hljs-string">'2018-01-01'</span> <span class="hljs-keyword">WHEN</span> MATCHED <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">SET</span> M.status<span class="hljs-operator">=</span> T.status</pre></div><div id="8dac" class="link-block"> <a href="https://cloud.google.com/blog/products/data-analytics/optimizing-your-bigquery-incremental-data-ingestion-pipelines?hl=en"> <div> <div> <h2>How to reduce the cost of BigQuery incremental data ingestion pipelines | Google Cloud Blog</h2> <div><h3>Improve performance of BigQuery incremental data ingestion pipelines by pruning more data during merge step</h3></div> <div><p>cloud.google.com</p></div> </div> <div> <div style="background-image: url(https://miro.readmedium.com/v2/resize:fit:320/0*pWbHB-Mrxn4MM91i)"></div> </div> </div> </a> </div><h1 id="49c1">Reference</h1><div id="7706" class="link-block"> <a href="https://documents.trocco.io/docs/how-to-merge-updated-rows-to-master-table-on-dwh"> <div> <div> <h2>DWH上のマスターテーブルを重複なく効率的に更新する</h2> <div><h3>本ページでは、DWHのテーブルに対し、「データの重複なく」「効率的に」変更を反映する方法をご紹介します。 転送先Google BigQueryには、 UPSERTや MERGEといった転送モードがありません。…</h3></div> <div><p>documents.trocco.io</p></div> </div> <div> <div style="background-image: url(https://miro.readmedium.com/v2/resize:fit:320/0*BRQiHxA0J2rFZG_D)"></div> </div> </div> </a> </div></article></body>

Praha, Solo

Because I could; that’s why (and so can you)

Golem in the Old Town Square on Day One (He was gone the next day)

When I ended the relationship I’d been in for nearly five years in 2008, I had the stunning realization that this was the first time I’d been single since I was 18. What? I decided it was time to take this houseplant off the window sill and pop her out into the yard where she could get some real sun on all sides.

Which is why I decided that I was going to Prague on my own in 2009 to see Leonard Cohen. No one else was up for it (dummies).

This was during his marathon five-year global tour and, yes, I know I could have seen him right here at Radio City Music Hall or even in his hometown of Montreal. I do know that. But Prague had been calling me ever since I’d read that much of it had been spared the brutal bombing of World War II. And seeing Leonard sing “The Partisan” or “First We Take Manhattan” in Prague? Please.

What a show!

Getting tickets to the concert was tricky as they weren’t available online (I bought two because I knew I’d find someone in Prague who would want to see this show and I did). In fact, the only way to purchase tickets was to send Czech kronas by postal mail to the box office in Prague. And when, due to the exchange rate, I sent too little, I had to trot on over to Chase bank and get more kronas to send to the CR (Czech Republic as it is now known and if you ask me about my trip to Czechoslovakia I will swat your nose with a rolled up newspaper).

This wasn’t my first solo European trip. About two and a half years earlier I celebrated earning my BA from Columbia University by spending a week in Venice (Italy) on the credit card. My blessed mentor, Anita Dixon Eppley, back in Ohio urged me to go ahead and do it that way. I’m so glad I did and one of these days a link to that story will live right here:

When I Went to Venice The Week After Carnival

The view from the end of “my” street in the Dorsoduro

So the passport had been wiggling and whispering ever since that trip and all it took was learning that Leonard would be doing a show in Prague to have me ready to go.

This is where I launch into my obligatory spiel on how important it is for everyone (especially those of us of the female persuasion) to travel by ourselves to a country where we don’t speak the language. I will concede that learning Czech before going would be incredibly impressive and kind of insane. I know expats who lived and worked there for years without managing to speak the language so there’s that.

Back to my point.

The apartment I locked myself out of on Day One in Prague

Go by yourself to a country where you don’t speak the language. You will amaze yourself with your smarts and resilience. It’s going to be a lot harder for anyone to push you around after you’ve dealt with being locked out of your apartment in a decidedly sketchy-looking part of town with your passport and all your kronas in the apartment. And, yes, this is the voice of experience.

Many better writers than I have long extolled the virtues of solo traveling. All I can add is my own experience. I stood there, wondering if I should stop a police officer after being unable to get the key to work in the door of the apartment I’d rented for the week (hotels are for tourists, right?). Here’s a surprise: I didn’t cry. I decided against asking that officer for help and went back into the hallway of the building and waited for inspiration. Instead I got a miracle. A neighbor who spoke English stepped out of his apartment, knew my landlady and had her number. And this time she showed me the “trick” for turning the key the right way.

Sunrise over The Vltava River with the The Church of Mother of God before Týn in the distance

When you’re going to be on your own in another country you have to make plans. Make these plans before you arrive and then stick to them. Otherwise you’ll find yourself sitting in your room wondering what on earth possessed you to do such a bone-headed thing. I had plans. And not just the usual touristy things, but I did some of those, too. One of the less-touristy things was a trip to the Parachutists’ Church.

Remember my buddy, Pete, from this story?

Yeah, that Pete. Pete was a huge history buff and he instructed me to go to the Cathedral Church of Sts Cyril and Methodius, also known as The Parachutists’ church.

The parachutists in question were in hiding in this church in 1942 after assassinating The Butcher of Prague, Reichsprotektor Reinhard Heydrich. As may be surmised by the pockmarks of long-ago bullets that still pepper the outside of the church, the paratroopers didn’t survive.

I would have liked to call Pete and check in from my assignment, but the SIM card I bought to use in my cell phone didn’t work. That meant that for eight days I was phone-less and it was glorious. I did occasionally feel a phantom buzz in my pocket but mostly I reveled in the freedom of not being so damned connected.

Church of Our Lady before Týn (Chrám Matky Boží před Týnem) — Turned out to be a very handy landmark

Thanks to mutual friends I did have some contacts awaiting me in Prague and I wasted no time in reaching out. That landed me at lunch at a Thai restaurant hidden in the stairwell between two buildings. It’s who ya know, baby. That’s also how I met Morton.

Now check this out: because Morton was a Danish poet, Denmark used some of its tax revenue to send him (and I guess all the other Danish artists) each year to spend a month in a city of his choosing. Just like that. I’d gotten two tickets because I knew that somewhere in Prague there would be a Morton, someone who’d really love that show. And this Morton did.

Courtesy of Rama — WikiCommons

And what a show. The old guy was electric. Just on fire in that smoldering, old fire kind of way. There must have been ten times during that concert when I was nearly in tears. The opening chords of so many songs blew away years of crusty built up not-remembering. And, even in Prague, more than half the audience sang along with nearly every song. After a full two hours onstage, Leonard took a long bow and thanked us all again. He left the stage. People began to leave but more people stayed put. Sure enough, out he came for an encore. And then another one and another one. I lost count after seven. That concert ran well over four hours. Morton and I hung out after the show, talking a little, but mostly just sitting there in the late summer evening as the crowds jammed onto the subway (I would like to note here that Prague’s subways run smoothly and quietly on their rubber wheels and are clean but did sport an inordinate number of white supremacist stickers).

The next day we met up for lunch and went Golem hunting because Prague. Here’s what the internetz has to tell you about the Golem: a late 16th century rabbi of Prague reportedly created a golem out of clay from the banks of the Vltava River and brought it to life through rituals and Hebrew incantations to defend the Prague ghetto from anti-Semitic attacks and pogroms. The Golem’s body was stored in the attic of the Old New Synagogue where it would be restored to life again if needed. So off we went to The Jewish Cemetery and The Old New Synagogue.

We did not find our Golem (mostly because neither of was ready to pay the entrance fee to the Old New Synagogue), but the cemetery was stunning. Because of Europe’s long tradition of antisemitism the Jews of Prague were forced to bury their dead in layers over the centuries in the same tiny plot of land which has resulted in a picturesque pile of grave markers leaning in all directions.

The Old New Synagogue where the body of the Golem is supposed to be
The Jewish Cemetery

We had a marvelous day together and enjoyed a delicious dinner in an Italian restaurant hidden in a courtyard. Morton, like others I met there, warned me against eating at any of the Czech restaurants (a warning I ignored and am glad I did because the food was much better than I’d been led to believe and was remarkably affordable). The next day I was on my own again.

A real check-mark item in Prague is catching a classical music concert in one of the numerous cathedrals and I was definitely up for that. As I walked away from the ticket broker with my ticket for the concert, someone behind me asked if I knew where the church was. We found it together and had a great time. Kate was Australian, a lovely strapping blonde, who had just arrived in Prague having just finished her odyssey across Asia and Europe aboard the Siberian Express (so much for my being the intrepid solo traveler of the evening!).

Another friend I’d made through those mutual friends was Martin, an Anglo-Irish fellow who created to-scale models of automobiles for Škoda Auto. After having walked and trammed and subwayed my way around Prague for the week, it was a special treat to ride around the city on my last night in Martin’s British BMW (to be clear; this was Martin, not Morton, who was ready by this time to move on for his upcoming month in Reykjavik). I was a bit sweet on Martin and had asked for the ride since I’d never ridden on the “wrong” side of a car before. I was struck by how small the part of the city was where I’d done most of my wandering.

When we got back to my apartment there was this moment. I’d hung out with Martin a fair bit during the week and thought I’d made my interest in him clear. But as we sat there in the car chatting I realized that if anyone was making a move that night it was going to have to be me. I moved out of the car.

Don’t get me wrong; I am quite comfortable initiating things with people I find attractive and interesting (ask my current partner). But that night I was pretty sure that if I made the first move in that silver British BMW, it would be a move going nowhere. Not even to a fun little one night stand. Who needs the grief?

I’m crazy grateful that I had the chance to travel solo when I did. It wasn’t always fun and there were some messed up things that happened, but even the messes were important. I’m a much more interested and interesting person because of having flown off to new places by myself.

Admit it; there’s someplace in this world that you’ve been wanting to go and haven’t. Maybe it’s the money but if it’s because no one’s up for joining you, grab that passport and book your flight. Do it now.

Random Prague Beauty
Prague’s Favorite Son: Franz Kafka
The Ladies of Prague

Thanks for reading all the way to here!

© Remington Write 2019. All Rights Reserved.

Solo Travel
Travel
Europe
World War II
Discovery
Recommended from ReadMedium