Developers Club geek daily blog

Reefs of use of Excel Power Query and MySQL for automation of the reporting

1 year, 3 months ago
image
All hi.
There came new 2016, so it is time to update tools for simplification of boring mechanical work. Departments of analytics, marketing, sales often face the following difficulties when updating the reporting:
1. Data should be aggregated from several sources.
2. Reports are formed in Excel that imposes considerable restrictions for volume of the processed data.
3. Modification of the unloadings which are in advance configured by developers business as a rule not the faster.

If reports need to be updated weekly or even daily, then this procedure becomes very napryazhny even for the most patient. By means of a superstructure of Excel Power Query and data record in MySQL it is possible to reduce updating of the majority of reports before simple clicking of the Update button:
1. Data from any quantity of sources are imported through SQL queries to the normal tables Excel.
2. Even from big base it is possible to write in Excel only small part of data (for example, total sums for the necessary range of dates with grouping only on the necessary columns).
3. Changes can be made to the report just having changed the SQL query. Further we create the necessary report standard means of Excel.

I will show how to configure and autocomplete the simple MySQL databases (on the example of unloading of statistics of all key word from Metrics Yandex) in this article, and then one button to update reports in Excel, using Power Query superstructure. Power Query has very strange features of work by drawing up SQL queries (especially dynamic) which we will sort in the second part of article.

Read more »


Service from the Percona company for creation of an optimum configuration of MySQL of servers and the analysis of SQL queries

1 year, 4 months ago
I suggest to examine service from the Percona company which allows to configure correctly a server MySQL configuration on the basis of specific conditions of use and to analyze the used SQL queries on existence of errors and defects.



The analysis of requests in this service — is not replacement to the EXPLAIN command which is oriented to performance review of request, and is rather an addition which analyzes request from the point of view of its syntax.

Read more »


DataGrip release (ex-0xDBE) 1.0 — new IDE for SQL

1 year, 4 months ago
Hi! We let out IDE for work with databases.

One and a half years we did 0xDBE according to the program of early access (EAP). And here, we understood that it is time to put end under our work. We thank all who tried 0xDBE on the projects and wrote us — you very much helped. We will miss this name too.

Now IDE is called DataGrip.



The supported DBMS

DataGrip is universal IDE for work with MySQL, PostgreSQL, Oracle, SQL Server, Sybase, DB2, SQLite, HyperSQL, Apache Derby and H2.

Work with objects of a DB and code generation

DataGrip provides tools for work with database objects. If you create or change the table, add or change a column, an index, a key in already existing, use the graphic interface. Similar changes are followed by generation of the corresponding script — you can execute the made changes in base at once or copy the generated DDL request in the editor and work already directly with a code.


Read more »


NGINX as the balancer of loading for MySQL or MariaDB Galera Cluster

1 year, 5 months ago
This article is transfer of original article from the website Severalnines.

Nginx is well-known all for the enhanced features and efficiency as a proxy and/or the balancer of web applications with low consumption of memory. As a rule, nginx is used on the first "line of defense" of web applications to distribute load of servers of a bekend, periodically checking their working capacity. This technology is quite popular for applications which need the increased fault tolerance.


Read more »


The most important argument against MySQL?

1 year, 5 months ago
Recent series of articles ("Instruction of the evangelist of PostgreSQL: we criticize MySQL competently" 1,2,3) hooked for live.

So it turned out that my command inherited, isteorichesk the developed system, with 300+ objects where MySQL acts as one of key components of system. On some objects replication is also used. Software the using MySQL from the third-party developer.

Read more »


Asterisk: Let's provide to VIP clients the first place in queue of calls, and we will also connect the client with the specific operator on preset time

1 year, 5 months ago
Asterisk is fun!
Every time facing some non-standard task I rejoice, rejoice opportunities again to plunge into this wonderful status of creativity, a brainwork. Recently such tasks appear often and it is healthy.
Designated in heading were implemented and work, so time to share the solutions with community came.

I will tell a little in more detail about everyone.

1. To organize the list of phone numbers of VIP clients.
Calls from VIP clients have to get on the first place to queues of Asterisk, for the fastest processing of their address. It is also necessary to have a feature to conveniently add and delete partners from this list.

2. To connect the client's call with the specific operator of queue on preset time.
To configure Asterisk so that in his "memory", on some preset time, there was information on queue, what of operators, accepted a challenge. The person from number 8913*75*5*0 called and gets to the operator of queue of Alyon and it is necessary to make so that during, for example days, the entering calls from this number were received only by Alyona and nobody else.
But there is more to come, if the client does not want to communicate with Alyona, then he can key * on the phone and next time will get already to other operator.

With the introduction on it I finish, it is a little Python, MySQL and cunning dialplan wait for you under a cat.

Read more »


Instruction of the evangelist of PostgreSQL: replicants against replication

1 year, 5 months ago


Throughout a series of the publications "Instruction of the Evangelist of PostgreSQL..." (1, 2) expensive edition contacts again, the promised overview of replicaiton engines in PostgreSQL and MySQL this time. The frequent criticism of replication of MySQL was the main cause for writing. As it often happens, the typical criticism is strong mix from the truth, a half-truth and evangelism. All this is repeatedly replicated by different people without special attempts to understand heard. And as it is quite extensive subject, I decided to take out analysis in the separate publication.

Read more »


Hera NoSQL behind

1 year, 6 months ago

New trend on HighLoad ++ — a set of reports on use of random access memory. The word to Konstantin Osipov, the developer of the Tarantool platform, the author of the report "That special in DBMS for data in random access memory".

You were responsible in MySQL for performance how so it turned out that you decided to develop the DBMS?
In MySQL I directed one of commands of development of the server, all were responsible for performance there.

MySQL in many parameters was work of dream, but, unfortunately after we became part of Oracle, a lot of things changed.

Several my colleagues went to MariaDB, someone founded the company (SeveralNines, FromDual). I never felt "underloaded", and with departure of many key developers work in general turned into a marathon on transfer of knowledge. Resistance to absorption, desire to start everything with a clean slate, revolt against slow decision-making by the big company, unwillingness to go for various reasons to the USA, eventually, the good sentence from Mail.Ru to which by this moment there was already about a year — and I left.

If knew where I leave, ten more times would think. Sometimes there was no belief at all that it will be possible to make something useful, than will use outside Mail.Ru and now Tarantool is very far from "ideal DBMS" so far.

Read more »


Creation of the MySQL database within Microsoft Azure for students

1 year, 6 months ago
Quite recently declared Microsoft providing free cloudy hosting of Microsoft Azure to students. Within the program such resources as are available to students: websites, the WebAPI and SignalR, CMS Joomla web services, and also the MySQL databases (yes, MS SQL is still unavailable).



On the promo-site Microsoft Azure series of video lessons from shwars devoted to elements of creation of the sites in the environments WebMatrix, Visual Studio and example of deployment of the site on Joomla is available to students (which it is also available in text option).

In this article I would like to add this series and to describe — as it is possible to connect the MySQL database to the project on. net Framework. As this process integrates in itself some entities (Azure, MySQL and the Visual Studio), it will be stated most in detail and in pictures, for the beginning students programmers.
Therefore, carefully traffic!

Read more »


The correct migration with MyISAM on InnoDB

1 year, 6 months ago
Let me distract you from cats and I will tell, based on the experience, what reefs appear upon transition with MyISAM to InnoDB and as to avoid them. The application code will be for PHP.

I have decided to write this post, having read huge number of the wrong replies to the request from subject on the Internet. On all Internet illiterate or not full answers therefore there is impression that to smigrirovat your database on InnoDB — it very simply are scattered. No, it is not simple! So, we will begin!

Why to pass to InnoDB


With this question, I think, everything is clear to all. I will not explain — are devoted to advantages of InnoDB a lot of articles on the Internet. If you read these lines then you have consciously arrived at this idea of transfer of the economy into InnoDB, and you, habrayuzer, google) I Hope, this article — that is necessary to you.

Preparatory stage

1. From the banal — it to provide necessary amount of free space on disk where we have unrolled base. InnoDB takes about 1,5 times more places, than MyISAM.

2. Very important point — it is useful to you in the future at trablshuting performance of ishshus in base. It is necessary to comment on each SQL request in your application with use of unique identifier, for example, of sequence number. If you have hundreds or thousands of SQL of requests how you lived still without it?

SELECT /*017*/ client_id, money, lastname FROM clients WHERE money > 100;

Read more »