blocks|key|1632201|text|BigQuery和外部表容量使datalake+(文件)和数据仓库(结构化数据)之间的边界变得模糊，您的问题也是相关的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1632202|当您使用外部表时，缺少了几个特性，比如集群和分区，并且您的文件被动态解析(使用类型转换)+->处理时间更慢，并且无法控制/限制进程的数据量。此外，文件中可能出现的错误会破坏查询。|1632203|使用本机表时，对BigQuery处理、数据已经清理和分析、表已分区和群集的数据存储进行了优化。|1632204|1632205|成本问题是一个很难的多重问题。首先，我们可以讨论数据存储。如果GCS中有文件，而BigQuery中有相同的数据，则需要支付两次存储费用。然而，在没有任何更新的90天后，数据进入BigQuery中的“存档”存储模式，成本降低了2倍。此外，在集成到BigQuery之后，还可以将GCS文件移动到一个冷存储区。|1632206|那是储藏室的。然后是处理。首先，处理成本大约是存储成本的10倍，这是最需要关注的事情。执行BigQuery请求时，需要支付查询扫描的数据量。如果有分区或集群，使用BigQuery本机表，则可以限制扫描的数据量，从而大大降低成本。对于外部表，您不能使用分区和群集特性，因此始终需要支付全部数据。|1632207|因此，这取决于(一如既往)您的数据量和请求的频率。|1632208|1632209|不要忘记一些额外的东西:对于外部表，您可能会有可能中断查询的错误。在生产模式中，它可以是戏剧性的。在这件事上想聪明点。|1632210|最后，请求外部表比本机表慢(没有分区，因此需要处理更多数据和解析/转换持续时间)。因为时间就是金钱(如果你有时间关键的查询)，而非物质成本也会影响你的选择。|1632211|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|X|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Y|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|Z|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|11|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|12|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|S|$]]

BigQuery and the external table capacity make the border between datalake (file) and data warehouse (structured data) blurry, and your question is relevant.
When you use external table, several feature are missing, like clustering and partitioning, and your file are parsed on the fly (with type casting) -&gt; the processing time is slower and you can't control/limit the volume of data that your process. In addition of possible errors in file that will break your query
When you use native table, the data storage is optimize for the BigQuery processing, the data already clean and parsed, the table partitioned and clustered.
<hr />
The question of cost is hard multiple. Firstly, we can talk about data storage. if you have file in GCS and the same data in BigQuery, you will pay the storage twice. However, after 90 days without any update, the data goes to &quot;archive&quot; storage mode in BigQuery and are 2 time cheaper. In addition, you can also move your GCS file to a cold storage after their integration in BigQuery.
That's for the storage. Then the processing. First of all, the processing roughly cost 10 times more than the storage, and it's the most important things to focus on. When you perform a BigQuery request, you pay for the volume of data that your query scan. If you have partitions or clusters, with BigQuery native tables, you can limit the amount of data that you scan and therefore reduce a lot the cost. With external tables, you can't use partitioning and clustering feature and therefore you always pay for the full amount of data.
Therefore, it depends (as always) on your volume of data and the frequency of the requests.
<hr />
Don't forget something additional: with external table you can have error that can break your queries. In production mode, it can be dramatic. Think smart on that.
Finally, requesting external table is slower that native table (no partitioning, therefore more data to process and parsing/casting duration). Because time is money (if you have time critical queries), and that immaterial cost can also influence your choices.

blocks|key|773457|text|@guillaume+blaquiere的答案是可以的，但他忘了提到一些重要的事情:可以执行分区查询。可以创建链接到存储区中的桶的分区外部表。例：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|773458|773459|gs://myBucket/myTable/dt=2019-10-31/lang=en/foo|unordered-list-item|773460|773461|773462|gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar|773463|773464|然后，您可以在来自BigQuery的SQL查询中使用"dt“或"lang”过滤器。|773465|https://cloud.google.com/bigquery/docs/hive-partitioned-queries-gcs|773466|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/11372593/guillaume-blaquiere|1^0|0|K|0|0|0|0|0|0|0|0|0|0|1V|1|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|D|3|-4|5|6|7|13|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|14|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|16|8|@]|9|@]|C|$]]|$1|J|3|K|5|G|7|17|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|Q|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|P]]]]

The <a href="https://stackoverflow.com/users/11372593/guillaume-blaquiere">@guillaume blaquiere</a> answer is okay, but he forget mention something important: it is possible to do partitioned queries. You can create partitioned external tables linked to a bucket in the storage. Eg:
<ul>
<li>gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
</li>
<li>gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar
</li>
</ul>
Then, you can use &quot;dt&quot; or &quot;lang&quot; filters in SQL queries from BigQuery.
<a href="https://cloud.google.com/bigquery/docs/hive-partitioned-queries-gcs" rel="nofollow noreferrer">https://cloud.google.com/bigquery/docs/hive-partitioned-queries-gcs</a>

My team is working on developing data platform using Google Cloud Platform.
We uploaded our company's data on Google Cloud Storage and try to make data mart on Bigquery.
However, in order to save GCP usage cost, we are considering to load all data from gcs to bigquery or create external table on bigquery.
Which way is more cost efficienct?

Bigquery Pricing Comparison : Loading data into Bigquery vs Using Create External Table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的团队正在使用Google平台开发数据平台。我们将公司的数据上传到上，并尝试在Bigquery上创建数据集市。但是，为了节省GCP的使用成本，我们考虑将所有数据从gcs加载到bigquery，或者在bigquery上创建外部表。哪种方式更符合成本效益？

问Bigquery定价比较:将数据加载到Bigquery使用Create外部表
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Bigquery定价比较:将数据加载到Bigquery使用Create外部表EN