投稿時間:2020-11-13 09:52:53 RSSフィード2020-11-13 09:00 分まとめ(57件)

カテゴリー等	サイト名等	記事タイトル・トレンドワード等	リンクURL	頻出ワード・要約等/検索ボリューム	登録日
IT	気になる、記になる…	｢AirPods Studio｣のアイコン画像?? − ｢iOS 14.3 beta｣から新たな画像が見つかる	https://taisy0.com/2020/11/13/127586.html		2020-11-12 23:30:17
IT	気になる、記になる…	Appleと携帯大手3社、｢iPhone 12 mini｣と｢iPhone 12 Pro Max｣を発売	https://taisy0.com/2020/11/13/127546.html	Appleと携帯大手社、「iPhonemini」と「iPhoneProMax」を発売本日、Appleが、「iPhonemini」と「iPhoneProMax」を発売しました。	2020-11-12 23:24:07
IT	気になる、記になる…	Apple、｢Compressor 4.5｣をリリース	https://taisy0.com/2020/11/13/127584.html	apple	2020-11-12 23:20:29
IT	気になる、記になる…	Apple、｢Motion 5.5｣をリリース	https://taisy0.com/2020/11/13/127582.html	apple	2020-11-12 23:17:50
IT	気になる、記になる…	Apple、｢Logic Remote 1.5｣をリリース	https://taisy0.com/2020/11/13/127579.html	apple	2020-11-12 23:15:51
IT	気になる、記になる…	Apple、｢MainStage 3.5｣をリリース	https://taisy0.com/2020/11/13/127576.html	apple	2020-11-12 23:12:41
IT	気になる、記になる…	Apple、｢Logic Pro X 10.6｣をリリース	https://taisy0.com/2020/11/13/127573.html	apple	2020-11-12 23:07:37
IT	気になる、記になる…	Apple、｢Final Cut Pro 10.5｣をリリース	https://taisy0.com/2020/11/13/127525.html	apple	2020-11-12 23:02:55
TECH	Engadget Japanese	MCUドラマ『ワンダヴィジョン』、2021年1月にディズニープラスで配信	https://japanese.engadget.com/wandavision-232550378.html	disney	2020-11-12 23:25:50
IT	ITmedia 総合記事一覧	[ITmedia News] Instagram、［発見］タブ位置に［リール］タブ　米国では［ショップ］タブも	https://www.itmedia.co.jp/news/articles/2011/13/news063.html	facebook	2020-11-13 08:33:00
AWS	AWS Partner Network (APN) Blog	Say Hello to 83 New AWS Competency, Service Delivery, Service Ready, and MSP Partners Added in October	https://aws.amazon.com/blogs/apn/say-hello-to-83-new-aws-competency-service-delivery-service-ready-and-msp-partners-added-in-october/	Say Hello to New AWS Competency Service Delivery Service Ready and MSP Partners Added in OctoberWe are excited to highlight APN Partners that received new designations in April for our global AWS Competency AWS Managed Service Provider MSP AWS Service Delivery and AWS Service Ready programs These designations span workload solution and industry and help AWS customers identify top APN Partners that can deliver on core business objectives APN Partners are focused on your success helping customers take full advantage of the business benefits AWS has to offer	2020-11-12 23:59:42
AWS	AWS Partner Network (APN) Blog	How Ensono Delivered Guinness World Records’ Award-Winning Migration of Windows Workloads to AWS	https://aws.amazon.com/blogs/apn/how-ensono-delivered-guinness-world-records-award-winning-migration-of-windows-workloads-to-aws/	How Ensono Delivered Guinness World Records  Award Winning Migration of Windows Workloads to AWSMany organizations are cautious about moving to the cloud or are fighting against gravity by staying on premises To keep up with their community s needs Guinness World Records has had to reimagine their IT architecture Learn how Ensono helped Guinness World Records move all in on AWS over a period of months and explore how Ensono continues to help them innovate using advanced AWS services	2020-11-12 23:53:49
AWS	AWS Mobile Blog	Integrating Existing Applications Into DevOps with AWS Amplify	https://aws.amazon.com/blogs/mobile/4707-2/	Integrating Existing Applications Into DevOps with AWS AmplifyMany customers are interested in DevOps or more specifically how to make small incremental changes to their applications and have that small change rapidly deployed through stages of test and verification into production These changes could be either Application code changes Changes to the supporting infrastructure the code runs on Integrated resource the code relies …	2020-11-12 23:27:59
python	Pythonタグが付けられた新着投稿 - Qiita	DeepChemのGraphGatherLayerをPyTorchのカスタムレイヤーで実装する	https://qiita.com/kimisyo/items/7935d43b86072e3d2ef0	DeepChemのGraphGatherLayerをPyTorchのカスタムレイヤーで実装するはじめにGraphConvLayerGraphPoolLayerに続いて、DeepChemのGraphGatherLayerをPytorchのカスタムレイヤーで実装してみた。	2020-11-13 08:16:53
Program	[全てのタグ]の新着質問一覧｜teratail（テラテイル）	service実行時のpythonプログラムの実行の仕方がわからない	https://teratail.com/questions/303965?rss=all	service実行時のpythonプログラムの実行の仕方がわからない前提・実現したいことserviceを使って自動起動プログラムを作成しています。	2020-11-13 08:55:33
Program	[全てのタグ]の新着質問一覧｜teratail（テラテイル）	pycharmでpsycopg2が読み込めていない	https://teratail.com/questions/303964?rss=all	pycharmでpsycopgが読み込めていない前提・実現したいことpycharmでrunserverを動かそうとしたところ、下記の表示が出てエラーになりました。	2020-11-13 08:53:24
Program	[全てのタグ]の新着質問一覧｜teratail（テラテイル）	ＵＷＳＣで　画像認識させる方法	https://teratail.com/questions/303963?rss=all	UWSCで画像認識させる方法いつもお世話になっております。	2020-11-13 08:41:51
Program	[全てのタグ]の新着質問一覧｜teratail（テラテイル）	Wordpress 指定のページに飛ばない。	https://teratail.com/questions/303962?rss=all	下記画像のように、hearderphpからブログをクリックするとarchivephpへ飛べるようにリンクを指定しています。	2020-11-13 08:35:35
Git	Gitタグが付けられた新着投稿 - Qiita	Gitで間違えたブランチにAddやCommitしてしまった時に使うコマンド	https://qiita.com/shichiria/items/1811442bfb7b4505e48d	featurebに変更を入れたいのに、実はcommitをfeatureaでやってしまっていたこれでcommitを取り消しできます。	2020-11-13 08:17:16
海外TECH	Ars Technica	Cruise line resumed voyages in Caribbean. It’s not going well	https://arstechnica.com/?p=1722954	industry	2020-11-12 23:15:14
海外TECH	Ars Technica	Report: White House pressuring CISA to stop debunking election nonsense	https://arstechnica.com/?p=1722925	election	2020-11-12 23:03:30
Apple	AppleInsider - Frontpage News	iOS 14.3 beta offers 'AirTags' setup, operation information	https://appleinsider.com/articles/20/11/12/ios-143-beta-offers-airtags-setup-operation-information	iOS beta offers x AirTags x setup operation informationApple s first iOS beta which was briefly released Thursday contains more evidence that the company is still developing AirTags Credit AppleCode strings and other data that reference the Bluetooth and Ultra Wideband tracking accessory have been found in a slew of previous betas The new evidence in iOS just confirms that Apple is still working on the device for a possible near future release Read more	2020-11-12 23:47:47
Apple	AppleInsider - Frontpage News	First iOS 14.3 beta adds ProRAW format, PS5 controller support, more	https://appleinsider.com/articles/20/11/12/first-ios-143-beta-adds-proraw-format-ps5-controller-support-more	First iOS beta adds ProRAW format PS controller support moreApple s new ProRAW format will seemingly arrive with iOS alongside new third party app suggestions PS controller support and other features Credit AppleOn Thursday Apple issued the first iOS beta to registered developers for testing purposes but quickly pulled the updates due to cascading server issues However some were able to download the beta before it was removed Read more	2020-11-12 23:28:24
Apple	AppleInsider - Frontpage News	'AirPods Studio' design potentially revealed in iOS 14.3 beta	https://appleinsider.com/articles/20/11/12/airpods-studio-design-potentially-revealed-in-ios-143-beta	x AirPods Studio x design potentially revealed in iOS betaApple s widely rumored AirPods Studio headphone design has potentially leaked in the form of an icon discovered in the latest iOS beta release Spelunking through Apple s iOS beta code which was quickly pulled after an initial release on Thursday toMac spotted an abstract drawing of what could be the rumored over the ear headphone design Seen above the unnamed icon bears a striking resemblance to supposedly leaked photos of a high end Apple headphone that sports a two earcups attached to a headband with metal arms Leakers have claimed that pivots on the arms allow the earcups to swivel similar to designs seen by Grado and Bang and Olufsen Read more	2020-11-12 23:41:24
海外TECH	Engadget	Plague Inc.'s new 'The Cure' mode is free until the coronavirus pandemic ends	https://www.engadget.com/plague-inc-the-cure-mode-now-available-233222554.html	Plague Inc x s new x The Cure x mode is free until the coronavirus pandemic endsAnnounced back in March Plague Inc s The Cure update is now available on iOS and Android The new mode reverses the title s usual gameplay loop in which you design a virus to wipe out the human race Instead you ll need to implement measures such	2020-11-12 23:32:22
Cisco	Cisco Blog	Are enterprise meetings broken?	https://blogs.cisco.com/innovation/are-enterprise-meetings-broken	collaboration	2020-11-12 23:43:15
海外TECH	WIRED	Inside Parler, the Right's Favorite 'Free Speech' App	https://www.wired.com/story/parler-app-free-speech-influencers	Inside Parler the Right x s Favorite x Free Speech x AppThe top app on both Google and Apple s app stores this week promises conservatives a safe spaceーbut gives priority treatment to its most high profile users	2020-11-12 23:35:26
医療系	内科開業医のお勉強日記	COPD急性増悪リスク予測:好酸球比率の方が好酸球数より優秀？	https://kaigyoi.blogspot.com/2020/11/copd_13.html	副次的な目的は、好酸球数が多い群と少ない群の臨床的特徴を比較すること、血中好酸球数の異なるカットオフ値、、に関連した増悪頻度を調査すること、増悪リスクを鑑別するための最適な血中好酸球率または絶対数のカットオフ値を決定することであった。	2020-11-12 23:21:00
金融	金融総合：経済レポート一覧	リテール金融ビジネスのパラダイムシフトとは	http://www3.keizaireport.com/report.php/RID/435012/?rss	大和総研	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	【挨拶】わが国の経済・物価情勢と金融政策 長野県金融経済懇談会における挨拶要旨 日本銀行政策委員会審議委員 安達誠司	http://www3.keizaireport.com/report.php/RID/435014/?rss	安達誠司	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	中国「相互宝」の加入者の特性、加入理由、加入効果～中国「ネット互助プラン」が保険事業に与える影響に関する調査：基礎研レポート	http://www3.keizaireport.com/report.php/RID/435017/?rss	保険事業	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	FX Daily(11月11日)～ドル円、105円台半ばまで上昇	http://www3.keizaireport.com/report.php/RID/435022/?rss	fxdaily	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	世界経済の持続性確保に向けて求められる金融の役割 ～サステナブル・ファイナンスの現状と課題：Newsletter No.26	http://www3.keizaireport.com/report.php/RID/435023/?rss	newsletterno	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	驚くほど底堅い日本株 ～背景に工作機械受注：Market Flash	http://www3.keizaireport.com/report.php/RID/435063/?rss	marketflash	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	米国リート市場動向と見通し（2020年11月号）：REITレポート	http://www3.keizaireport.com/report.php/RID/435068/?rss	見通し	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	マーケットの視点「投資環境」：世界を支える強力なリフレ政策（吉川レポート）	http://www3.keizaireport.com/report.php/RID/435070/?rss	三井住友	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	バイデン氏の政策と市場への影響を考える：市川レポート	http://www3.keizaireport.com/report.php/RID/435071/?rss	三井住友	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	日銀が「地域金融強化のための特別当座預金制度」を創設	http://www3.keizaireport.com/report.php/RID/435083/?rss	当座預金	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	金融安定理事会による「グローバルなシステム上重要な銀行（G-SIB）の2020年リスト」の公表について	http://www3.keizaireport.com/report.php/RID/435085/?rss	日本銀行	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	金融安定理事会による「アウトソーシング・サードパーティに関する規制・監督上の論点（ディスカッション・ペーパー）」の公表について	http://www3.keizaireport.com/report.php/RID/435087/?rss	金融安定理事会	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	第201回　家計調査にみるコロナ禍における投資スタイル	http://www3.keizaireport.com/report.php/RID/435097/?rss	家計調査	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	投信マーケット MABアナリスト・アイ 2020年11月号	http://www3.keizaireport.com/report.php/RID/435102/?rss	発表	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	タイの社会的企業の経営実態と持続的発展に関する研究 / 社会保険における子どもの位置付けの強化に関する国際比較研究 / 医療保障における共済・民間保険の可能性 / 超高齢社会を支える介護保障システムの構築	http://www3.keizaireport.com/report.php/RID/435110/?rss	位置付け	2020-11-13 00:00:00
金融	金融総合：経済レポート一覧	令和２年 犯罪収益移転危険度調査書	http://www3.keizaireport.com/report.php/RID/435113/?rss	警察庁	2020-11-13 00:00:00
ニュース	BBC News - Home	Dominic Cummings to leave Downing Street by Christmas	https://www.bbc.co.uk/news/uk-politics-54925322	minister	2020-11-12 23:41:56
ニュース	BBC News - Home	Northern Ireland lose in extra time to Slovakia to miss out on Euros	https://www.bbc.co.uk/sport/football/54819939	Northern Ireland lose in extra time to Slovakia to miss out on EurosSlovakia beat Northern Ireland after extra time in the play off final at Windsor Park to book their place in next year s European Championship finals	2020-11-12 23:13:16
ニュース	BBC News - Home	Masters 2020: Paul Casey sets clubhouse lead on day one at Augusta	https://www.bbc.co.uk/sport/golf/54922012	Masters Paul Casey sets clubhouse lead on day one at AugustaPaul Casey holds the clubhouse lead at seven under par after day one of the Masters closes with half the field still to complete their rounds	2020-11-12 23:18:00
ニュース	BBC News - Home	Bellingham, 17, makes debut in England win over Republic of Ireland	https://www.bbc.co.uk/sport/football/54819958	Bellingham makes debut in England win over Republic of IrelandJude Bellingham becomes the third youngest player to represent England as they enjoy a routine win over the Republic of Ireland in a friendly at Wembley	2020-11-12 23:28:58
ニュース	BBC News - Home	The Masters 2020: Tiger Woods, Justin Rose & Phil Mickelson in best shots of day one	https://www.bbc.co.uk/sport/av/golf/54926280	mickelson	2020-11-12 23:30:07
ビジネス	ダイヤモンド・オンライン - 新着記事	米ディズニー、2四半期連続赤字　コロナ禍響く - WSJ発	https://diamond.jp/articles/-/254228	連続	2020-11-13 08:19:00
サブカルネタ	ラーブロ	長崎ちゃんぽん リンガーハット イオンモール日の出店＠西多摩郡日の出町<長崎ちゃんぽん・麺2倍>	http://feedproxy.google.com/~r/rablo/~3/26CVa_e1wbQ/single_feed.php	長崎ちゃんぽんリンガーハットイオンモール日の出店西多摩郡日の出町lt長崎ちゃんぽん・麺倍gt訪問日メニュー長崎ちゃんぽん味豚骨コメント今日紹介するのは、秋川駅・武蔵引田駅から離れた「イオンモール日の出」階フードコートにある「リンガーハット」。	2020-11-12 23:21:47
北海道	北海道新聞	尖閣に日米安保適用確認　首相、バイデン氏会談	https://www.hokkaido-np.co.jp/article/481083/	日米安保	2020-11-13 08:15:38
北海道	北海道新聞	バイデン氏に安保情報報告認めず　トランプ政権の対応、疑問視拡大	https://www.hokkaido-np.co.jp/article/481116/	米大統領選	2020-11-13 08:15:00
仮想通貨	BITPRESS（ビットプレス）	CoinGecko、2020年10月 月刊仮想通貨レポートを公開	https://bitpress.jp/count2/3_9_12052	coingecko	2020-11-13 08:41:48
仮想通貨	BITPRESS（ビットプレス）	[日経] ビットコイン、2年10カ月ぶり高値　決済利用増に期待	https://bitpress.jp/count2/3_9_12051	高値	2020-11-13 08:38:20
仮想通貨	BITPRESS（ビットプレス）	[cointelegraph] ペイパルが米国で仮想通貨サービスを開始	https://bitpress.jp/count2/3_9_12050	cointelegraph	2020-11-13 08:36:03
GCP	Cloud Blog	BigQuery Explained: Data Manipulation (DML)	https://cloud.google.com/blog/topics/developers-practitioners/bigquery-explained-data-manipulation-dml/	BigQuery Explained Data Manipulation DML In the previous posts of BigQuery Explained we reviewed how to ingest data into BigQuery and query the datasets In this blog post we will show you how to run data manipulation statements in BigQuery to add modify and delete data stored in BigQuery Let s get started Data Manipulation in BigQueryBigQuery has supported Data Manipulation Language DML functionality since for standard SQL which enables you to insert update and delete rows and columns in your BigQuery datasets DML in BigQuery supports data manipulation at an arbitrarily large number of rows in a table in a single job and supports an unlimited number of DML statements on a table This means you can apply changes to data in a table more frequently and keep your data warehouse up to date with the changes in data sources  In this blog post we will cover Use cases and syntax of common DML statementsConsiderations when using DML including topics like quotas and pricingBest practices for using DML in BigQueryFollowing tables will be used in the examples in this post TransactionsCustomerProductLet s start with DML statements supported by BigQuery and their usage INSERT UPDATE DELETE and MERGE INSERT statementINSERT statement allows you to append new rows to a table You can insert new rows using explicit values or by querying tables or views or using subqueries Values added must be compatible with the target column s data type Following are few patterns to add rows into a BigQuery table INSERT using explicit values This approach can be used to bulk insert explicit values INSERT using SELECTstatement This approach is commonly used to copy a table s content into another table or a partition Let s say you have created an empty table and plan to add data from an existing table for example from a public data set You can use the INSERT INTO …SELECT statement to append new data to the target table INSERT using subqueries or common table expressions CTE As seen in the previous post WITH statement allows you to name a subquery and use it in subsequent queries such as the SELECT or INSERT statement here also called Common Table Expressions In the example below values to be inserted are computed using a subquery that performs JOIN operation with multiple tables DELETE statementDELETE statement allows you to delete rows from a table When using a DELETE statement you must use WHERE clause followed by a condition  DELETE all rows from a tableDELETE FROM project dataset table WHERE true DELETE with WHEREclause This approach uses WHERE clause to identify the specific rows to be deleted DELETE FROM project dataset table WHERE price DELETE with subqueries This approach uses a subquery to identify the rows to be deleted The subquery can query other tables or perform JOINs with other tables DELETE project dataset table tWHERE t id NOT IN SELECT id from project dataset unprocessed UPDATE statementUPDATE statement allows you to modify existing rows in a table Similar to DELETE statement each UPDATE statement must include the WHERE clause followed by a condition To update all rows in the table use WHERE true Following are few patterns to update rows in a BigQuery table UPDATE with WHERE clause Use WHERE clause in the UPDATE statement to identify specific rows that need to be modified and use SET clause to update specific columns UPDATE using JOINs  In a data warehouse it s a common pattern to update a table based on conditions from another table In the previous example we updated quantity and price columns in the product table Now we will update the transactions table based on the latest values in the product table NOTE A row in the target table to be updated must match with at most one row when joining with the source table in the FROM clause Otherwise runtime error is generated UPDATE nested and repeated fields As seen in the previous post BigQuery supports nested and repeated fields using STRUCT and ARRAY to provide a natural way of denormalized data representation With BigQuery DML you can UPDATE nested structures as well In the product table specs is a nested structure with color and dimension attributes and the dimension attribute is a nested structure The below example UPDATEs the nested field for specific rows identified by WHERE clause MERGE statementMERGE statement is a powerful construct and an optimization pattern that combines INSERT UPDATE and DELETE operations on a table into an “upsert operation based on values matched from another table In an enterprise data warehouse with a star or snowflake schema a common use case is to maintain Slowly Changing Dimension SCD tables that preserves the history of data with reference to the source data i e insert new records for new dimensions added remove or flag dimensions that are not in the source and update the values that are changed in the source The MERGE statement can be used to manage these operations on a dimension table with a single DML statement Here is the generalized structure of the MERGE statement A MERGE operation performs JOIN between the target and the source based on merge condition Then depending on the match status MATCHED NOT MATCHED BY TARGET and NOT MATCHED BY SOURCE  corresponding action is taken The MERGE operation must match at most one source row for each target row When there is more than one row matched the operation errors out The following picture illustrates MERGE operation on the source and target tables with the corresponding actions INSERT UPDATE and DELETE MERGE operation can be used with source as subqueries joins nested and repeated structures Let s look at MERGE operation with INSERT else UPDATE pattern using subqueries In the below example MERGE operation INSERTs the row when there are new rows in source that are not found in target and UPDATEs the row when there are matching rows from both source and target tables You can also include an optional search condition in WHEN clause to perform operations differently In the below example we derive the price of Furniture products differently compared to other products  Note that when there are multiple qualified WHEN clauses only the first WHEN clause is executed for a row The patterns seen so far in this post is not an exhaustive list Refer to BigQuery documentation for DML syntax and more examples Things to know about DML in BigQueryUnder the HoodBigQuery performs the following steps when executing a DML job This is only a representative flow of what happens behind the scenes when you execute a DML job in BigQuery Note that when you execute a DML statement in BigQuery an implicit transaction is initiated that commits the transaction automatically when successful Refer this article to understand how BigQuery executes a DML statement Quotas and LimitsBigQuery enforces quotas for a variety of reasons such as to prevent unforeseen spikes in usage to protect the community of Google Cloud users There are no quota limits on BigQuery DML statements i e BigQuery supports an unlimited number of DML statements on a table However  you must be aware of following quotas enforced by BigQuery when designing the data mutation operations DML statements are not subjected to a quota limit but they do count towards the quota tables operations per day and partition modifications per day DML statements will not fail due to these limits but other jobs can Concurrent DML JobsBigQuery manages the concurrency of DML statements that mutate rows in a table BigQuery is a multi version and ACID compliant database that uses snapshot isolation to handle multiple concurrent operations on a table Concurrently running mutating DML statements on a table might fail due to conflicts in the changes they make and BigQuery retries these failed jobs Thus the first job to commit wins which could mean that when you run a lot of short DML operations you could starve longer running ones Refer this article to understand how BigQuery manages concurrent DML jobs How many concurrent DML jobs can be run INSERT DML job concurrency During any hour period you can run the first INSERT statements into a table concurrently After this limit is reached the concurrency of INSERT statements that write to a table is limited to Any INSERT DML jobs beyond are queued in PENDING state After a previously running job finishes the next PENDING job is dequeued and run Currently up to INSERT DML statements can be queued against a table at any given time UPDATE DELETE and MERGE DML job concurrency BigQuery runs a fixed number of concurrent mutating DML statements UPDATE DELETE or MERGE on a table When the concurrency limit is reached BigQuery automatically queues the additional mutating DML jobs in a PENDING state After a previously running job finishes the next PENDING job is dequeued and run Currently BigQuery allows up to mutating DML jobs to be queued in PENDING state for each table and any concurrent mutating DMLs beyond this limit will fail This limit is not affected by concurrently running load jobs or INSERT DML statements against the table since they do not affect the execution of mutation operations  What happens when concurrent DML jobs get into conflicts DML conflicts arise when the concurrently running mutating DML statements UPDATE DELETE MERGE try to mutate the same partition in a table and may experience concurrent update failures Concurrently running mutating DML statements will succeed as long as they don t modify data in the same partition In case of concurrent update failures BigQuery handles such failures automatically by retrying the job by first determining a new snapshot timestamp to use for reading the tables used in the query and then applying the mutations on the new snapshot BigQuery retries concurrent update failures on a table up to three times Note that inserting data to a table does not conflict with any other concurrently running DML statement You can mitigate conflicts by grouping DML operations and performing batch UPDATEs or DELETEs Pricing DML StatementsWhen designing DML operations in your system it is key to understand how BigQuery prices DML statements to optimize costs as well as performance BigQuery pricing for DML queries is based on the number of bytes processed by the query job with DML statement Following table summarizes the calculation of bytes processed based on table being partitioned or non partitioned Since the DML pricing is based on the number of bytes processed by the query job the best practices of querying the data with SELECT queries applies to DML query jobs as well For example limiting the bytes read by querying only data that is needed partition pruning with partitioned tables block pruning with clustered tables and more  Following are best practices guides for controlling bytes read by a query job and optimizing costs Managing input data and data sources BigQueryEstimating storage and query costs BigQueryCost optimization best practices for BigQueryDMLs on Partitioned and Non Partitioned TablesIn the previous BigQuery Explained post we perceived how BigQuery partitioned tables make it easier to manage and query your data improve the query performance and control costs by reducing bytes read by a query In the context of DML statements partitioned tables can accelerate the update process when the changes are limited to the specific partitions For example a DML statement can update data in multiple partitions for both ingestion time partitioned and partitioned tables date timestamp datetime and integer range partitioned Let s refer to the example from the partitioning section of BigQuery Explained Storage Overview post where we created non partitioned and partitioned tables from a public data set based on StackOverflow posts  Non Partitioned TablePartitioned TableLet s run an UPDATE statement on non partitioned and partitioned tables to modify a column for all the StackOverflow posts created on a specific date Non Partitioned TablePartitioned TableIn this example with the partitioned table the query with DML job scans and updates only the required partition processing MB data compared to the DML job on the non partitioned table that processes GB data doing a full table scan Here the DML operation on the partitioned table is faster and cheaper than the non partitioned table Using DML statements INSERT UPDATE DELETE MERGE with partitioned and non partitioned tables follow the same DML syntax as seen in the post earlier Except when working with an ingestion time partitioned table you specify the partition refering the PARTITIONTIME pseudo column For example see the INSERT statement below for ingestion time partitioned table and a partitioned table INSERT with ingestion time partitioned tableINSERT with partitioned TableWhen using a MERGE statement against a partitioned table you can limit the partitions involved in the DML statements by using partition pruning conditions in a subquery filter a search condition filter or a merge condition filter Refer BigQuery documentation for using DML with partitioned tables and non partitioned tables DML and BigQuery Streaming insertsIn the BigQuery Explained Data Ingestion post we touched upon the streaming ingestion pattern that allows continuous styles of ingestion by streaming data into BigQuery in real time using the tabledata insertAll method BigQuery allows DML modifications on tables with active streaming buffer based on recency of writes in the table Rows written to the table recently using streaming cannot be modified Recent writes are typically those that occur within the last minutes  All other rows in the table are modifiable with mutating DML statements UPDATE DELETE or MERGE Best Practices Using DML in BigQueryAvoid point specific DML statements Instead group DML operations Even though you can now run unlimited DML statements in BigQuery consider performing bulk or large scale mutations for the following reasons  BigQuery DML statements are intended for bulk updates Using point specific DML statements is an attempt to treat BigQuery like an Online Transaction Processing OLTP system BigQuery focuses on Online Analytical Processing OLAP by using table scans and not point lookups Each DML statement that modifies data initiates an implicit transaction By grouping DML statements you can avoid unnecessary transaction overhead DML operations are charged based on the number of bytes processed by the query which can be a full table or partition or cluster scan By grouping DML statements you can limit the number of bytes processed DML operations on a table are subjected to rate limiting when multiple DML statements are submitted too quickly By grouping operations you can mitigate the failures due to rate limiting The following are a few ways to perform bulk mutations Batch mutations by using the MERGE statement based on contents of another table MERGE statement is an optimization construct that can combine INSERT UPDATE and DELETE operations into one statement and perform them atomically Using subqueries or correlated subqueries with DML statements where the subquery identifies the rows to be modified and the DML operation mutates data in bulk Replace single row INSERTs with bulk inserts using explicit values or subqueries or common table expressions CTE as discussed earlier in the post For example if you have the following point specific INSERT statements running them as is in BigQuery is an anti pattern You can translate into a single INSERT statement that performs a bulk operation instead If your use case involves frequent single row inserts consider streaming your data instead Please note there is a charge for streamed data unlike load jobs which are free Refer BigQuery documentation on examples of performing batch mutations Batch your updates and inserts Performing large scale mutations in BigQueryUse CREATE TABLE AS SELECT CTAS for large scale mutations DML statements can get significantly expensive when you have large scale modifications For such cases prefer CTAS CREATE TABLE AS SELECT instead So instead of performing a large number of UPDATE or DELETE statements you run a SELECT statement and save the query results into a new target table with modified data using CREATE TABLE AS SELECT operation After creating the new target table with modified data you would discard the original target table SELECT statements can be cheaper than processing DML statements in this case  Another typical scenario where a large number of INSERT statements is used is when you create a new table from an existing table Instead of using multiple INSERT statements create a new table and insert all the rows in one operation using the CREATE TABLE AS SELECT statement Use TRUNCATE when deleting all rows When performing a DELETE operation to remove all the rows from a table use TRUNCATE TABLE statement instead The TRUNCATE TABLE statement is a DDL Data Definition Language operation that removes all rows from a table but leaves the table metadata intact including the table schema description and labels Since TRUNCATE is a metadata operation it does not incur a charge TRUNCATE TABLE project dataset mytable Partition your data As we have seen earlier in the post partitioned tables can significantly improve performance of DML operation on the table and optimize cost as well Partitioning ensures that the changes are limited to specific partitions within the table For example when using MERGE statement you can lower cost by precomputing the partitions affected prior to the MERGE and include a filter for the target table that prunes partition in a subquery filter a search condition filter or a merge condition filter of MERGE statement If you don t filter the target table the mutating DML statement will do a full table scan  In the following example you are limiting the MERGE statement to scan only the rows in the partition in both the source and the target table by specifying a filter in the merge condition When UPDATE or DELETE frequently modify older data or within a particular range of dates consider partitioning your tables Avoid partitioning tables if the amount of data in each partition is small and each update modifies a large fraction of the partitions Cluster tablesIn the previous post of BigQuery Explained we have seen clustering data can improve performance of certain queries by sorting and collocating related data in blocks If you often update rows where one or more columns fall within a narrow range of values consider using clustered tables Clustering performs block level pruning and scans only data relevant to the query reducing the number of bytes processed by the query This improves DML query performance as well as optimizes costs You can use clustering with or without partitioning the table and clustering the tables is free Refer example of DML query with clustered tableshere  Be mindful of your data editsIn the previous post of BigQuery Explained we mentioned long term storage can offer significant price savings when your table or partition of a table has not been modified for days There is no degradation of performance durability availability or any other functionality when a table or partition is considered for long term storage  To get the most out of long term storage be mindful of any actions that edit your table data such as streaming copying or loading data including any DML or DDL actions Any modification can bring your data back to active storage and reset the day timer To avoid this you can consider loading the new batch of data to a new table or a partition of a table  Consider Cloud SQL for OLTP use casesIf your use case warrants OLTP functionality consider using Cloud SQL federated queries which enable BigQuery to query data that resides in Cloud SQL Check out this video for querying Cloud SQL from BigQuery Querying Cloud SQL from BigQueryWhat s Next In this article we learned how you can add modify and delete data stored in BigQuery using DML statements how BigQuery executes DML statements best practices and things to know when working with DML statements in BigQuery Check out BigQuery documentation on DML statementsUnderstand quotas limitations and pricing of BigQuery DML statements Refer to this blog post on BigQuery DML without limitsIn the next post we will look at how to use scripting stored procedures and user defined functions in BigQuery Stay tuned Thank you for reading Have a question or want to chat Find me on Twitter or LinkedIn Thanks to Pavan Edara and Alicia Williams for helping with the post Related Article New blog series BigQuery explained An overviewOur new blog series provides an overview of what s possible with BigQuery Read Article	2020-11-12 23:30:00